const express = require("express");
const router = express.Router();
const path = require("path");
const pool = require("../pool.js");
const poolmomo = require("../poolmomo.js");
const poolTarot = require("../poolTarot.js");
const poolmuseo = require("../poolmuseo.js");
const utils = require("../utils/index.js");
const utilsServer = require("../utils/serverInfo.js");
const moment = require("moment-timezone");
const multer = require("multer");
const AdmZip = require("adm-zip");
const AppInfoParser = require("app-info-parser");
const plist = require("plist");
const fs = require("fs");
const { readManifest } = require("node-aab-parser");
const msgUtils = require("../utils/feishu.js");
const crypto = require("crypto");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");
const { exec } = require("child_process");
const config = require("../config");
const axios = require("axios");
const jwt = require("jsonwebtoken");
const jwksClient = require('jwks-rsa');
const xlsx = require('xlsx');

// 创建 JWKS 客户端
router.get('/exportWeeklyRenewalRatesCsv', async (req, res) => {
    try {
        const { pk } = req.query;
        if (!pk) return res.status(400).send('缺少必需的 pk 参数');

        const sql = `
            SELECT *
            FROM new_weekly_renewal_rates
            WHERE pk = ?
            ORDER BY base_date ASC
        `;
        const data = await mysqlQuery(sql, [pk]);
        if (!data || data.length === 0) return res.status(404).send('无数据');

        // 移除特殊首条（2024/09/06 这一天）
        const firstItem = data[0];
        if (firstItem) {
            const baseDate = new Date(firstItem.base_date);
            const chinaTime = new Intl.DateTimeFormat('zh-CN', {
                timeZone: 'Asia/Shanghai',
                year: 'numeric',
                month: '2-digit',
                day: '2-digit'
            }).format(baseDate);
            if (chinaTime === '2024/09/06') {
                data.shift();
            }
        }

        // ========== 日期工具 ==========

        // 获取北京时间的今天
        function getTodayInChina() {
            const now = new Date();
            const utc = now.getTime() + now.getTimezoneOffset() * 60000;
            return new Date(utc + 8 * 3600000);
        }
        // 获取排除今天，连续的最近3天 [前3天, 前2天, 昨天]
        function getRecentChinaDatesExcludeToday(n = 3) {
            let today = getTodayInChina();
            let result = [];
            for (let i = n; i >= 1; i--) {
                let dt = new Date(today.getFullYear(), today.getMonth(), today.getDate() - i);
                const y = dt.getFullYear();
                const m = String(dt.getMonth() + 1).padStart(2, '0');
                const d = String(dt.getDate()).padStart(2, '0');
                result.push(`${y}-${m}-${d}`);
            }
            return result;
        }
        // base_date转北京时间 yyyy-MM-dd
        function toChinaYMD(date) {
            if (!date) return '';
            let jsDate = (typeof date === 'string') ? new Date(date) : date;
            const utc = jsDate.getTime() + jsDate.getTimezoneOffset() * 60000;
            const beijing = new Date(utc + 8 * 3600000);
            const y = beijing.getFullYear();
            const m = String(beijing.getMonth() + 1).padStart(2, '0');
            const d = String(beijing.getDate()).padStart(2, '0');
            return `${y}-${m}-${d}`;
        }

        // ========== 核心逻辑开始 ==========

        const targetDates = getRecentChinaDatesExcludeToday(3); // 只补今天前面三个自然日
        const dataToPatch = data
            .map(item => ({ item, chinaDate: toChinaYMD(item.base_date) }))
            .filter(x => targetDates.includes(x.chinaDate))
            .filter(x => !x.item.daily_trial_count || x.item.daily_trial_count == 0);
        const baseDatesToPatch = dataToPatch.map(x => x.chinaDate);

        console.log('排除今天，最近3天:', targetDates);
        console.log('需要补 daily_trial_count 的日期:', baseDatesToPatch);

        // 去数据库补数
        if (baseDatesToPatch.length > 0) {
            const qMarks = baseDatesToPatch.map(_ => '?').join(',');
            const patchSql = `
                SELECT DATE(create_date) as d, COUNT(*) as c
                FROM js_app_apple_notifications
                WHERE pk = ?
                  AND subtype = 'INITIAL_BUY'
                  AND offer_discount_type = 'FREE_TRIAL'
                  AND DATE(create_date) IN (${qMarks})
                GROUP BY DATE(create_date)
            `;
            const patchParams = [pk, ...baseDatesToPatch];
            const patchRows = await mysqlQuery(patchSql, patchParams);

            const patchMap = {};
            patchRows.forEach(row => {
                patchMap[toChinaYMD(row.d)] = row.c;
            });

            dataToPatch.forEach(x => {
                if (patchMap[x.chinaDate] !== undefined) {
                    x.item.daily_trial_count = patchMap[x.chinaDate];
                    console.log(`补充 ${x.chinaDate} 的 daily_trial_count 为 ${patchMap[x.chinaDate]}`);
                }
            });
        }

        // ========== 后续导出逻辑不变 ==========
        const formattedData = data.map(item => {
            let ratesArr = [];
            try {
                ratesArr = item.rates ? JSON.parse(item.rates) : [];
            } catch (e) {
                ratesArr = [];
            }
            const newItem = {
                base_date: item.base_date,
                initial_count: item.initial_count,
                daily_trial_count: item.daily_trial_count
            };
            ratesArr.forEach((rate, idx) => {
                newItem[`week_${idx + 1}_date`] = rate.week;
                newItem[`week_${idx + 1}_count`] = rate.count;
                newItem[`week_${idx + 1}_rate`] = rate.rate;
            });
            return newItem;
        });

        let maxWeeks = 0;
        formattedData.forEach(row => {
            let weeks = 0;
            Object.keys(row).forEach(key => {
                if (/week_\d+_rate/.test(key)) weeks++;
            });
            maxWeeks = Math.max(maxWeeks, weeks);
        });

        const cleanPk = String(pk).replace(/[^\w.-]/g, '_');
        const safeFileName = `weekly_renewal_${cleanPk}_${Date.now()}.csv`;
        const headers = {
            base_date: '基准日期',
            initial_count: '基准续订数',
            daily_trial_count: '日试用数',
        };
        for (let i = 1; i <= maxWeeks; i++) {
            headers[`week_${i}_date`] = `第${i}周日期`;
            headers[`week_${i}_count`] = `第${i}周续费数`;
            headers[`week_${i}_rate`] = `第${i}周续费率`;
        }
        const toExportData = formattedData.map(item => {
            const obj = {};
            for (const key in headers) {
                if (key === 'base_date' && item[key]) {
                    const date = new Date(item[key]);
                    obj[headers[key]] = date.toLocaleDateString('zh-CN', { timeZone: 'Asia/Shanghai' });
                } else if (/week_\d+_rate/.test(key) && item[key] !== undefined) {
                    obj[headers[key]] = (item[key] * 100).toFixed(2) + '%';
                } else {
                    obj[headers[key]] = item[key] !== undefined ? item[key] : '';
                }
            }
            return obj;
        });
        let csv = xlsx.utils.sheet_to_csv(
            xlsx.utils.json_to_sheet(toExportData),
            { FS: ",", RS: "\n", skipHeader: false }
        );
        csv = "\uFEFF" + csv;
        res.setHeader('Content-Type', 'text/csv;charset=utf-8');
        res.setHeader('Content-Disposition', `attachment; filename="${safeFileName}"`);
        res.send(csv);

    } catch (err) {
        console.error('导出 CSV 出错:', err);
        res.status(500).send('导出 CSV 发生错误。');
    }
});



 
function dateToYMD(date) {
    if (!date) return '';
    if (typeof date === 'string') return date.slice(0, 10);
    if (date instanceof Date) return date.toISOString().slice(0, 10);
    return '';
  }
router.get('/exportAppleYearlyBuyRenewCsv', async (req, res) => {
    try {
        const { pk } = req.query;
        if (!pk) {
            return res.status(400).send('缺少必需的 pk 参数');
        }

        // 查询分组统计
        const sql = `
            SELECT 
                DATE(signed_date) AS stat_date,
                COUNT(*) AS count
            FROM js_app_apple_notifications
            WHERE 
                pk = ?
                AND product_id = 'yearly_plan'
                AND (
                    subtype = 'INITIAL_BUY'
                    OR notification_type = 'DID_RENEW'
                )
            GROUP BY DATE(signed_date)
            ORDER BY stat_date ASC
        `;
        const data = await mysqlQuery(sql, [pk]);
        if (!data || data.length === 0) {
            return res.status(404).send('无数据');
        }

        // 组装导出内容
        const headers = {
            stat_date: '统计日期',
            count: '人数'
        };

        const toExportData = data.map(item => {
            return {
                [headers.stat_date]: item.stat_date,
                [headers.count]: item.count,
            }
        });

        // 生成 csv 字符串
        let csv = xlsx.utils.sheet_to_csv(
            xlsx.utils.json_to_sheet(toExportData),
            { FS: ",", RS: "\n", skipHeader: false }
        );
        csv = "\uFEFF" + csv;

        // 英文安全名字
        const cleanPk = String(pk).replace(/[^\w.-]/g, '_');
        const safeFileName = `apple_yearly_buy_renew_${cleanPk}_${Date.now()}.csv`;
        res.setHeader('Content-Type', 'text/csv;charset=utf-8');
        res.setHeader('Content-Disposition', `attachment; filename="${safeFileName}"`);
        res.send(csv);

    } catch (err) {
        console.error('导出 CSV 出错:', err);
        res.status(500).send('导出 CSV 发生错误。');
    }
});
router.get('/exportAppleOneTimeChargeCsv', async (req, res) => {
    try {
        const { pk } = req.query;
        if (!pk) {
            return res.status(400).send('缺少必需的 pk 参数');
        }

        // 每天统计 ONE_TIME_CHARGE 数量
        const sql = `
            SELECT 
                DATE(signed_date) AS stat_date,
                COUNT(*) AS count
            FROM js_app_apple_notifications
            WHERE 
                pk = ?
                AND notification_type = 'ONE_TIME_CHARGE'
            GROUP BY DATE(signed_date)
            ORDER BY stat_date ASC
        `;
        const data = await mysqlQuery(sql, [pk]);
        if (!data || data.length === 0) {
            return res.status(404).send('无数据');
        }

        const headers = {
            stat_date: '统计日期',
            count: '人数'
        };

        const toExportData = data.map(item => ({
            [headers.stat_date]: item.stat_date,
            [headers.count]: item.count,
        }));

        let csv = xlsx.utils.sheet_to_csv(
            xlsx.utils.json_to_sheet(toExportData),
            { FS: ",", RS: "\n", skipHeader: false }
        );
        csv = "\uFEFF" + csv;

        const cleanPk = String(pk).replace(/[^\w.-]/g, '_');
        const safeFileName = `apple_one_time_charge_${cleanPk}_${Date.now()}.csv`;
        res.setHeader('Content-Type', 'text/csv;charset=utf-8');
        res.setHeader('Content-Disposition', `attachment; filename="${safeFileName}"`);
        res.send(csv);
    } catch (err) {
        console.error('导出 CSV 出错:', err);
        res.status(500).send('导出 CSV 发生错误。');
    }
});
// 导出每日统计数据CSV
router.get('/exportAppSubscriptionStatsCsv', async (req, res) => {
    try {
        const { pk } = req.query;
        if (!pk) {
            return res.status(400).send('缺少必需的 pk 参数');
        }

        // 查询数据
        const sql = `
            SELECT 
                stat_date,
                product_id,
                successful_renewals,
                successful_refunds,
                trial_users,
                direct_purchase_users,
                one_time_purchase_users,
                trial_to_paid_count,
                trial_conversion_rate
            FROM js_app_subscription_daily_stats
            WHERE pk = ?
            ORDER BY stat_date ASC, product_id ASC
        `;
        const data = await mysqlQuery(sql, [pk]);
        if (!data || data.length === 0) {
            return res.status(404).send('无数据');
        }

        // 字段与表头
        const headers = {
            stat_date: '统计日期',
            product_id: '产品ID',
            successful_renewals: '成功续订',
            successful_refunds: '退款人数',
            trial_users: '试用人数',
            direct_purchase_users: '直接购买人数',
            one_time_purchase_users: '一次性购买',
            trial_to_paid_count: '试用转正人数',
            trial_conversion_rate: '试用转正率'
        };

        // 转换数据字段名为中文表头
        const toExportData = data.map(item => ({
            [headers.stat_date]: item.stat_date,
            [headers.product_id]: item.product_id,
            [headers.successful_renewals]: item.successful_renewals,
            [headers.successful_refunds]: item.successful_refunds,
            [headers.trial_users]: item.trial_users,
            [headers.direct_purchase_users]: item.direct_purchase_users,
            [headers.one_time_purchase_users]: item.one_time_purchase_users,
            [headers.trial_to_paid_count]: item.trial_to_paid_count,
            [headers.trial_conversion_rate]: ((item.trial_conversion_rate || 0) * 100).toFixed(2) + '%'
        }));

        let csv = xlsx.utils.sheet_to_csv(
            xlsx.utils.json_to_sheet(toExportData),
            { FS: ",", RS: "\n", skipHeader: false }
        );
        csv = "\uFEFF" + csv; // 解决Excel打开中文乱码

        // 文件名
        const cleanPk = String(pk).replace(/[^\w.-]/g, '_');
        const filename = `app_subscription_stats_${cleanPk}_${Date.now()}.csv`;

        res.setHeader('Content-Type', 'text/csv;charset=utf-8');
        res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
        res.send(csv);
    } catch (err) {
        console.error('导出 CSV 出错:', err);
        res.status(500).send('导出 CSV 发生错误。');
    }
});
// 将 Excel 日期序列号转换为 JavaScript 日期
function excelDateToJSDate(excelDate) {
    const startDate = new Date(1899, 11, 30); // Excel日期是从1900/1/1开始，但是有个误差
    return new Date(startDate.getTime() + excelDate * 24 * 60 * 60 * 1000);
}

// 将 '10/2/24' 转换为 'YYYY-MM-DD' 格式
function formatDateString(dateStr) {
    // 通过 moment 解析日期，并指定年为21世纪
    return moment(dateStr, 'M/D/YY').format('YYYY-MM-DD');
}

const upload = multer({ dest: 'uploads/' });
router.post('/uploadExcel', upload.single('file'), async (req, res) => {
    const pk = req.body.pk
    try {
        // 1. 获取上传的文件路径
        const filePath = req.file.path;

        // 2. 使用xlsx直接读取Excel文件
        const workbook = xlsx.readFile(filePath);
        const sheetName = workbook.SheetNames[0]; // 获取第一个工作表的名称
        const worksheet = workbook.Sheets[sheetName];

        // 3. 转换Excel内容为JSON
        const jsonData = xlsx.utils.sheet_to_json(worksheet, { raw: false });

        // 4. 处理日期字段和数值字段
        jsonData.forEach(row => {
            for (let key in row) {
                // 处理日期序列号
                if (typeof row[key] === 'number' && row[key] > 40000) {
                    row[key] = excelDateToJSDate(row[key]);
                }

                // 处理类似 '10/2/24' 的日期格式
                if (typeof row[key] === 'string' && /^\d{1,2}\/\d{1,2}\/\d{2}$/.test(row[key])) {
                    row[key] = formatDateString(row[key]);
                }
            }
        });

        for (const { date, count } of jsonData) {
            let checkSql = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ? AND base_date = ?`;
            let checkParams = [pk, date];
            const checkResult = await mysqlQuery(checkSql, checkParams);

            if (checkResult.length > 0) {
                // 如果找到记录，更新 daily_trial_count
                let updateSql = `UPDATE new_weekly_renewal_rates SET daily_new_count = ? WHERE pk = ? AND base_date = ?`;
                let updateParams = [count, pk, date];
                await mysqlQuery(updateSql, updateParams);
            } else {

            }
        }

        // 删除上传的临时文件
        fs.unlinkSync(filePath);
        res.json({
            success: true,
            message: 'File uploaded and parsed successfully!',
            data: jsonData
        });

    } catch (error) {
        console.error('Error while parsing Excel:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to upload and parse the Excel file.',
            error: error.message
        });
    }
});

router.post("/getPkList", async (req, res) => {

    let obj = req.body;
    let { page, size } = utils.pageSize(obj.page, obj.size);

    let sqlParams = [];

    let sql = `SELECT pk FROM apple_refund_notifications`;

    sql += ` GROUP BY pk LIMIT ?, ?`;
    sqlParams.push(page, size); // 注意，LIMIT子句中的参数调整了位置

    await pool.query(sql, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));

        const filteredResult = result.filter((item) => item.pk);
        filteredResult.forEach((item) => {
            item.package_name = item.pk;
        });

        res.send(
            utils.returnData({
                data: utils.mapPackageNamesToShortNames(filteredResult),
            })
        );
    });
});
//获取项目列表
router.post('/extractPackageNames', async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);

    let sql = `SELECT DISTINCT pk FROM js_app_apple_notifications;`
    let result = await mysqlQuery(sql)
    if (result.length != 0) {
        result.forEach(item => {
            item.package_name = item.pk
        })
        result.push({
            pk: 'com.trans.translationAiAndSubtitle',
            package_name: 'com.trans.translationAiAndSubtitle'
        })
    }
    if (name == 'zj_luqi' || name == 'zj_xiewenjing') {
        result = result.filter(item =>
            ['com.seamobitech.UniversalTVRemoteControl', 'com.zhijian.remoteControl', 'com.seamobitech.roku'].includes(item.pk)
        );
    }
    res.send(
        utils.returnData({
            data: utils.mapPackageNamesToShortNames(result)
        })
    );
})
//根据包名查询订阅通知管理列表
router.post('/getSubsByPkgName', async (req, res) => {
    const { pk, notification_type, storefront, offer_discount_type, dateRange, subtype, dateRange2, dateRange3 } = req.body;
    let { page, size } = utils.pageSize(req.body.page, req.body.size);

    // 基础的 SQL 查询
    let sql = `FROM js_app_apple_notifications WHERE pk = ?  AND (state IS NULL OR state <> 1)`;
    let params = [pk];

    // 处理可选字段
    if (notification_type) {
        sql += ` AND notification_type = ?`;
        params.push(notification_type);
    }
    if (storefront) {
        sql += ` AND storefront = ?`;
        params.push(storefront);
    }
    if (offer_discount_type) {
        if (offer_discount_type === 1) {
            sql += ` AND offer_discount_type = 'FREE_TRIAL'`;
        } else if (offer_discount_type === 2) {
            sql += ` AND (offer_discount_type IS NULL OR offer_discount_type != 'FREE_TRIAL')`;
        }
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += ` AND update_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (dateRange2 && dateRange2.start && dateRange2.end) {
        const startDate = moment(dateRange2.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange2.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND original_purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (dateRange3 && dateRange3.start && dateRange3.end) {
        const startDate = moment(dateRange3.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange3.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (subtype) {
        sql += ` AND subtype = ?`;
        params.push(subtype);
    }
    // 统计总条数的 SQL
    let sqlCount = `SELECT COUNT(*) AS total_count ${sql}`;
    // 查询数据的 SQL
    let sqlSelect = `SELECT * ${sql} ORDER BY update_date DESC`;

    try {
        let { total } = await utils.getListSum(sqlCount, params, res, req);

        // 查询数据
        sqlSelect += ` LIMIT ? OFFSET ?`;
        params.push(size, page);

        await pool.query(sqlSelect, params, async (err, result) => {
            if (err) return res.send(utils.returnData({ code: -1, err, req }));

            // 处理日期格式
            result.forEach((element) => {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.purchase_date = moment(element.purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.recent_subscription_start_date = moment(element.recent_subscription_start_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.renewal_date = moment(element.renewal_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.original_purchase_date = moment(element.original_purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            });

            // 返回数据
            res.send(
                utils.returnData({
                    data: result,
                    total: total,
                })
            );
        });
    } catch (err) {
        res.send(utils.returnData({ code: -1, err, req }));
    }
});
router.post('/getSubsByPkgNameMOMO', async (req, res) => {
    const { pk, notification_type, storefront, offer_discount_type, dateRange, subtype, dateRange2, dateRange3 } = req.body;
    let { page, size } = utils.pageSize(req.body.page, req.body.size);

    // 基础的 SQL 查询
    let sql = `FROM js_app_apple_notifications WHERE pk = ?  AND (state IS NULL OR state <> 1)`;
    let params = [pk];

    // 处理可选字段
    if (notification_type) {
        sql += ` AND notification_type = ?`;
        params.push(notification_type);
    }
    if (storefront) {
        sql += ` AND storefront = ?`;
        params.push(storefront);
    }
    if (offer_discount_type) {
        if (offer_discount_type === 1) {
            sql += ` AND offer_discount_type = 'FREE_TRIAL'`;
        } else if (offer_discount_type === 2) {
            sql += ` AND (offer_discount_type IS NULL OR offer_discount_type != 'FREE_TRIAL')`;
        }
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += ` AND update_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (dateRange2 && dateRange2.start && dateRange2.end) {
        const startDate = moment(dateRange2.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange2.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND original_purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (dateRange3 && dateRange3.start && dateRange3.end) {
        const startDate = moment(dateRange3.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange3.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (subtype) {
        sql += ` AND subtype = ?`;
        params.push(subtype);
    }
    // 统计总条数的 SQL
    let sqlCount = `SELECT COUNT(*) AS total_count ${sql}`;
    // 查询数据的 SQL
    let sqlSelect = `SELECT * ${sql} ORDER BY update_date DESC`;

    try {
        let { total } = await utils.getListSumMOMO(sqlCount, params, res, req);

        // 查询数据
        sqlSelect += ` LIMIT ? OFFSET ?`;
        params.push(size, page);

        await poolmomo.query(sqlSelect, params, async (err, result) => {
            if (err) return res.send(utils.returnData({ code: -1, err, req }));

            // 处理日期格式
            result.forEach((element) => {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.purchase_date = moment(element.purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.recent_subscription_start_date = moment(element.recent_subscription_start_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.renewal_date = moment(element.renewal_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.original_purchase_date = moment(element.original_purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            });

            // 返回数据
            res.send(
                utils.returnData({
                    data: result,
                    total: total,
                })
            );
        });
    } catch (err) {
        res.send(utils.returnData({ code: -1, err, req }));
    }
});
router.post('/getRefundByPkgName', async (req, res) => {
    const { is_agree, pk, notification_type, storefront, offer_discount_type, dateRange, subtype, dateRange2, dateRange3 } = req.body;
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    const MOMO_PKS = [
        'com.trans.translationAiAndSubtitle',
        'com.TranslationAISubTitle'
    ];

    const isMomoPk = MOMO_PKS.includes(pk);

    const dbPool = isMomoPk ? poolmomo : pool;
    const getListSumFunc = isMomoPk ? utils.getListSumMOMO : utils.getListSum;
    // 基础的 SQL 查询
    let sql = `FROM apple_refund_notifications`;
    let params = [];
    if (pk) {
        sql += ` WHERE pk = ?`;
        params.push(pk);
    }


    if (is_agree) {
        sql += params.length ? ` AND is_agree = ?` : ` WHERE is_agree = ?`;
        params.push(is_agree);
    }

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += params.length ? ` AND update_date BETWEEN ? AND ?` : ` WHERE update_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);

    }

    if (dateRange2 && dateRange2.start && dateRange2.end) {
        const startDate = moment(dateRange2.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange2.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += params.length ? ` AND original_purchase_date BETWEEN ? AND ?` : ` WHERE original_purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    if (dateRange3 && dateRange3.start && dateRange3.end) {
        const startDate = moment(dateRange3.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange3.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += params.length ? ` AND purchase_date BETWEEN ? AND ?` : ` WHERE purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    // 统计总条数的 SQL
    let sqlCount = `SELECT COUNT(*) AS total_count ${sql}`;
    // 查询数据的 SQL
    let sqlSelect = `SELECT * ${sql} ORDER BY update_date DESC`;

    try {
        let { total } = await getListSumFunc(sqlCount, params, res, req);

        // 查询数据
        sqlSelect += ` LIMIT ? OFFSET ?`;
        params.push(size, page);

        await dbPool.query(sqlSelect, params, async (err, result) => {
            if (err) return res.send(utils.returnData({ code: -1, err, req }));

            // 处理日期格式
            result.forEach((element) => {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.purchase_date = moment(element.purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.recent_subscription_start_date = moment(element.recent_subscription_start_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.renewal_date = moment(element.renewal_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.original_purchase_date = moment(element.original_purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            });

            // 返回数据
            res.send(
                {
                    data: result,
                    total: total
                }
            );
        });

    } catch (err) {
        res.send(utils.returnData({ code: -1, err, req }));
    }
});

router.post('/getRefundByPkgNameUSD', async (req, res) => {
    const { is_agree, pk, notification_type, storefront, offer_discount_type, dateRange, subtype, dateRange2, dateRange3 } = req.body;
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    const MOMO_PKS = [
        'com.trans.translationAiAndSubtitle',
        'com.TranslationAISubTitle'
    ];

    const isMomoPk = MOMO_PKS.includes(pk);

    const dbPool = isMomoPk ? poolmomo : pool;
    const getListSumFunc = isMomoPk ? utils.getListSumMOMO : utils.getListSum;
    // 基础的 SQL 查询
    let sql = `FROM apple_refund_notifications`;
    let params = [];
    if (pk) {
        sql += ` WHERE pk = ?`;
        params.push(pk);
    }


    if (is_agree) {
        sql += params.length ? ` AND is_agree = ?` : ` WHERE is_agree = ?`;
        params.push(is_agree);
    }

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += params.length ? ` AND update_date BETWEEN ? AND ?` : ` WHERE update_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);

    }

    if (dateRange2 && dateRange2.start && dateRange2.end) {
        const startDate = moment(dateRange2.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange2.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += params.length ? ` AND original_purchase_date BETWEEN ? AND ?` : ` WHERE original_purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    if (dateRange3 && dateRange3.start && dateRange3.end) {
        const startDate = moment(dateRange3.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange3.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql += params.length ? ` AND purchase_date BETWEEN ? AND ?` : ` WHERE purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    // 统计总条数的 SQL
    let sqlCount = `SELECT COUNT(*) AS total_count ${sql}`;
    // 查询数据的 SQL
    let sqlSelect = `SELECT * ${sql} ORDER BY update_date DESC`;
    const processResults = (results) => {
        if (results.length == 0) {
            return [];
        }

        const exchangeRates = {
            USD: 1.0,
            GBP: 0.800395,
            MXN: 20.5173,
            SAR: 3.75,
            EGP: 50.6195,
            IDR: 16356.7,
            BRL: 5.76580,
            QAR: 3.64,
            ZAR: 18.4602,
            MYR: 4.45725,
            CAD: 1.42566,
            ILS: 3.58665,
            JPY: 154.191,
            INR: 86.8679,
            AED: 3.67250,
            AUD: 1.58852,
            PHP: 58.1535,
            EUR: 0.958330,
            COP: 4165.95,
            PKR: 278.922,
            NZD: 1.76690,
            KZT: 497.910,
            CZK: 24.0070,
            NGN: 1505.03,
            RUB: 92.6978,
            CLP: 956.458,
            TRY: 36.1147,
            KRW: 1447.60,
            PEN: 3.71032,
            VND: 25475.7,
            CNY: 7.28512,
            DKK: 7.14790,
            HKD: 7.78616,
            CHF: 0.911281,
            BGN: 1.87423,
            TZS: 2594.69,
            PLN: 3.98441,
            SGD: 1.34773,
            RON: 4.76974,
            THB: 33.8423,
            NOK: 11.2195,
            SEK: 10.8155
        };

        const currencyMap = new Map();
        results.forEach(item => {
            if (item.is_agree == 2) {
                const currency = item.currency;
                const price = parseFloat(item.price) || 0;

                // 获取货币的汇率，若不存在汇率，则视为0，相当于价格直接为0
                const exchangeRate = exchangeRates[currency] || 0;
                const priceInUSD = exchangeRate !== 0 ? price / exchangeRate : 0;

                if (currencyMap.has(currency)) {
                    const existing = currencyMap.get(currency);
                    existing.price += price;
                    existing.price_in_usd += priceInUSD;
                    existing.count += 1;
                } else {
                    currencyMap.set(currency, {
                        currency: currency,
                        price: price,
                        price_in_usd: priceInUSD,
                        count: 1
                    });
                }
            }
        });

        return Array.from(currencyMap.values());
    };
    try {
        let { total } = await getListSumFunc(sqlCount, params, res, req);



        await dbPool.query(sqlSelect, params, async (err, result) => {
            if (err) return res.send(utils.returnData({ code: -1, err, req }));

            // 处理日期格式
            result.forEach((element) => {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.purchase_date = moment(element.purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.recent_subscription_start_date = moment(element.recent_subscription_start_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.renewal_date = moment(element.renewal_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.original_purchase_date = moment(element.original_purchase_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            });
            const data2 = processResults(result);
            // 计算总的美元金额
            let totalUSD = 0;
            if (data2.length > 0) {
                totalUSD = data2.reduce((sum, item) => sum + item.price_in_usd, 0).toFixed(2);
            }
            // 返回数据
            res.send(
                {
                    data: result,
                    total: total,
                    totalUSD: totalUSD,
                }
            );
        });

    } catch (err) {
        res.send(utils.returnData({ code: -1, err, req }));
    }
});

router.post('/getHistoryList', async (req, res) => {
    const { pk, original_transaction_id } = req.body
    let sql = `select  * from js_app_apple_notifications where pk = ? and original_transaction_id = ?`
    let result = await mysqlQuery(sql, [pk, original_transaction_id])
    result.forEach((element) => {
        element.create_date = moment(element.create_date)
            .tz("Asia/Shanghai")
            .format("YYYY-MM-DD HH:mm:ss");
        element.update_date = moment(element.update_date)
            .tz("Asia/Shanghai")
            .format("YYYY-MM-DD HH:mm:ss");
        element.purchase_date = moment(element.purchase_date)
            .tz("Asia/Shanghai")
            .format("YYYY-MM-DD HH:mm:ss");
        element.recent_subscription_start_date = moment(element.recent_subscription_start_date)
            .tz("Asia/Shanghai")
            .format("YYYY-MM-DD HH:mm:ss");
        element.renewal_date = moment(element.renewal_date)
            .tz("Asia/Shanghai")
            .format("YYYY-MM-DD HH:mm:ss");
        element.original_purchase_date = moment(element.original_purchase_date)
            .tz("Asia/Shanghai")
            .format("YYYY-MM-DD HH:mm:ss");
    });
    res.send(
        utils.returnData({
            data: result
        })
    );
})
router.post('/getSubsByPkgNameAll', async (req, res) => {
    const { pk, notification_type, storefront, offer_discount_type, dateRange, dateRange2, dateRange3 } = req.body;

    // 基础的 SQL 查询
    let sql = `FROM js_app_apple_notifications WHERE pk = ?  AND (state IS NULL OR state <> 1)`;
    let params = [pk];

    // 处理可选字段
    if (notification_type) {
        sql += ` AND notification_type = ?`;
        params.push(notification_type);
    }
    if (storefront) {
        sql += ` AND storefront = ?`;
        params.push(storefront);
    }
    if (offer_discount_type === 1) {
        sql += ` AND offer_discount_type = 'FREE_TRIAL'`;
    } else if (offer_discount_type === 2) {
        sql += ` AND offer_discount_type IS NULL`;
    }
    if (dateRange && dateRange.start && dateRange.end) {
        sql += ` AND update_date BETWEEN ? AND ?`;
        params.push(dateRange.start, dateRange.end);
    }
    if (dateRange2 && dateRange2.start && dateRange2.end) {
        const startDate = dateRange2.start;
        const endDate = moment(dateRange2.end).endOf('day').format('YYYY-MM-DD HH:mm:ss'); // 设置为当天结束

        sql += ` AND original_purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (dateRange3 && dateRange3.start && dateRange3.end) {
        const startDate = dateRange3.start;
        const endDate = moment(dateRange3.end).endOf('day').format('YYYY-MM-DD HH:mm:ss'); // 设置为当天结束

        sql += ` AND purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    // 查询数据的 SQL
    let sqlSelect = `SELECT * ${sql} ORDER BY update_date DESC`;

    try {
        const promiseQuery = new Promise((resolve, reject) => {
            pool.query(sqlSelect, params, (err, result) => {
                if (err) {
                    return reject(err); // 出错则reject
                }
                resolve(result); // 成功则resolve结果
            });
        });

        let result = await promiseQuery;

        // 处理日期格式和其他转换
        result.forEach((element) => {
            // 处理 notification_type
            switch (element.notification_type) {
                case 'SUBSCRIBED':
                    element.notification_type = '用户订阅了自动续订';
                    break;
                case 'CONSUMPTION_REQUEST':
                    element.notification_type = '用户发起退款请求';
                    break;
                case 'DID_CHANGE_RENEWAL_PREF':
                case 'DID_CHANGE_RENEWAL_STATUS':
                    element.notification_type = '用户对订阅计划进行了更改';
                    break;
                case 'DID_FAIL_TO_RENEW':
                    element.notification_type = '用户因账单问题而无法续订，订阅进入账单重试期。';
                    break;
                case 'DID_RENEW':
                    element.notification_type = '用户订阅已成功续订';
                    break;
                case 'EXPIRED':
                    element.notification_type = '用户订阅已过期';
                    break;
                case 'REFUND':
                    element.notification_type = '已成功退款';
                    break;
                case 'REFUND_DECLINED':
                    element.notification_type = 'App Store 拒绝了用户退款请求';
                    break;
                case 'REFUND_REVERSED':
                    element.notification_type = 'App Store 因客户提出的争议而撤销了之前批准的退款';
                    break;
            }

            // 处理 subtype
            switch (element.subtype) {
                case 'INITIAL_BUY':
                    element.subtype = '用户首次订阅成功';
                    break;
                case 'AUTO_RENEW_DISABLED':
                    element.subtype = '用户禁用了订阅自动续订，或者用户申请退款后 App Store 禁用了订阅自动续订';
                    break;
                case 'AUTO_RENEW_ENABLED':
                    element.subtype = '用户已启用订阅自动续订';
                    break;
                case 'BILLING_RECOVERY':
                    element.subtype = '之前续订失败的已过期订阅已成功续订';
                    break;
                case 'BILLING_RETRY':
                    element.subtype = '订阅已过期，因为订阅在计费重试期结束之前未能续订';
                    break;
                case 'GRACE_PERIOD':
                    element.subtype = '由于账单问题，订阅续订失败';
                    break;
                case 'UPGRADE':
                    element.subtype = '用户已升级其订阅或跨级升级到具有相同期限的订阅';
                    break;
                case 'VOLUNTARY':
                    element.subtype = '用户禁用订阅自动续订后订阅已过期';
                    break;
                case 'DOWNGRADE':
                    element.subtype = '用户已降级其订阅或跨级订阅，降级将在下一个续订日期生效。';
                    break;
            }

            // 处理 status
            switch (element.status) {
                case '1':
                    element.status = '自动续订已激活';
                    break;
                case '2':
                    element.status = '自动续订订阅已过期';
                    break;
                case '3':
                    element.status = '自动续订订阅处于计费重试期';
                    break;
                case '4':
                    element.status = '自动续订订阅处于计费宽限期';
                    break;
                case '5':
                    element.status = '自动续订订阅已被撤销';
                    break;
            }

            // 处理 offer_discount_type
            if (element.offer_discount_type == 'FREE_TRIAL') {
                element.offer_discount_type = '免费试用';
            }

            // 处理 consumption_request_reasons
            switch (element.consumption_request_reason) {
                case 'UNINTENDED_PURCHASE':
                    element.consumption_request_reason = '客户无意进行应用内购买。';
                    break;
                case 'FULFILLMENT_ISSUE':
                    element.consumption_request_reason = '客户在接收或使用应用内购买时遇到了问题。';
                    break;
                case 'UNSATISFIED_WITH_PURCHASE':
                    element.consumption_request_reason = '客户对应用内购买不满意。';
                    break;
                case 'LEGAL':
                    element.consumption_request_reason = '客户基于法律原因要求退款。';
                    break;
                case 'OTHER':
                    element.consumption_request_reason = '客户因其他原因要求退款。';
                    break;
            }

            // 处理 revocation_reason
            switch (element.revocation_reason) {
                case '0':
                    element.revocation_reason = 'App Store 因其他原因（例如意外购买）代表客户退还了交易款项。';
                    break;
                case '1':
                    element.revocation_reason = '由于您的应用中存在实际问题或感知问题,App Store 代表客户退还了交易款项。';
                    break;
            }

            // 处理 auto_renew_status
            switch (element.auto_renew_status) {
                case '0':
                    element.auto_renew_status = '关闭';
                    break;
                case '1':
                    element.auto_renew_status = '开启';
                    break;
            }

            // 处理 expiration_intent
            switch (element.expiration_intent) {
                case '1':
                    element.expiration_intent = '用户取消了订阅';
                    break;
                case '2':
                    element.expiration_intent = '由于计费问题取消，如信用卡到期或支付失败。';
                    break;
                case '3':
                    element.expiration_intent = '用户从App Store Connect主动取消了订阅。';
                    break;
                case '4':
                    element.expiration_intent = '产品不可用时续订失败';
                    break;
            }
            element.create_date = moment(element.create_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.purchase_date = moment(element.purchase_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.recent_subscription_start_date = moment(element.recent_subscription_start_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.renewal_date = moment(element.renewal_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.original_purchase_date = moment(element.original_purchase_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });


        // 一旦我们处理完所有元素，返回结果给客户端
        res.send(utils.returnData({
            data: result
        }));
    } catch (err) {
        res.send(utils.returnData({ code: -1, err, req }));
    }
});

router.post('/getSubsByPkgNameAllMOMO', async (req, res) => {
    const { pk, notification_type, storefront, offer_discount_type, dateRange, dateRange2, dateRange3 } = req.body;

    // 基础的 SQL 查询
    let sql = `FROM js_app_apple_notifications WHERE pk = ?  AND (state IS NULL OR state <> 1)`;
    let params = [pk];

    // 处理可选字段
    if (notification_type) {
        sql += ` AND notification_type = ?`;
        params.push(notification_type);
    }
    if (storefront) {
        sql += ` AND storefront = ?`;
        params.push(storefront);
    }
    if (offer_discount_type === 1) {
        sql += ` AND offer_discount_type = 'FREE_TRIAL'`;
    } else if (offer_discount_type === 2) {
        sql += ` AND offer_discount_type IS NULL`;
    }
    if (dateRange && dateRange.start && dateRange.end) {
        sql += ` AND update_date BETWEEN ? AND ?`;
        params.push(dateRange.start, dateRange.end);
    }
    if (dateRange2 && dateRange2.start && dateRange2.end) {
        const startDate = dateRange2.start;
        const endDate = moment(dateRange2.end).endOf('day').format('YYYY-MM-DD HH:mm:ss'); // 设置为当天结束

        sql += ` AND original_purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    if (dateRange3 && dateRange3.start && dateRange3.end) {
        const startDate = dateRange3.start;
        const endDate = moment(dateRange3.end).endOf('day').format('YYYY-MM-DD HH:mm:ss'); // 设置为当天结束

        sql += ` AND purchase_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    // 查询数据的 SQL
    let sqlSelect = `SELECT * ${sql} ORDER BY update_date DESC`;

    try {
        const promiseQuery = new Promise((resolve, reject) => {
            poolmomo.query(sqlSelect, params, (err, result) => {
                if (err) {
                    return reject(err); // 出错则reject
                }
                resolve(result); // 成功则resolve结果
            });
        });

        let result = await promiseQuery;

        // 处理日期格式和其他转换
        result.forEach((element) => {
            // 处理 notification_type
            switch (element.notification_type) {
                case 'SUBSCRIBED':
                    element.notification_type = '用户订阅了自动续订';
                    break;
                case 'CONSUMPTION_REQUEST':
                    element.notification_type = '用户发起退款请求';
                    break;
                case 'DID_CHANGE_RENEWAL_PREF':
                case 'DID_CHANGE_RENEWAL_STATUS':
                    element.notification_type = '用户对订阅计划进行了更改';
                    break;
                case 'DID_FAIL_TO_RENEW':
                    element.notification_type = '用户因账单问题而无法续订，订阅进入账单重试期。';
                    break;
                case 'DID_RENEW':
                    element.notification_type = '用户订阅已成功续订';
                    break;
                case 'EXPIRED':
                    element.notification_type = '用户订阅已过期';
                    break;
                case 'REFUND':
                    element.notification_type = '已成功退款';
                    break;
                case 'REFUND_DECLINED':
                    element.notification_type = 'App Store 拒绝了用户退款请求';
                    break;
                case 'REFUND_REVERSED':
                    element.notification_type = 'App Store 因客户提出的争议而撤销了之前批准的退款';
                    break;
            }

            // 处理 subtype
            switch (element.subtype) {
                case 'INITIAL_BUY':
                    element.subtype = '用户首次订阅成功';
                    break;
                case 'AUTO_RENEW_DISABLED':
                    element.subtype = '用户禁用了订阅自动续订，或者用户申请退款后 App Store 禁用了订阅自动续订';
                    break;
                case 'AUTO_RENEW_ENABLED':
                    element.subtype = '用户已启用订阅自动续订';
                    break;
                case 'BILLING_RECOVERY':
                    element.subtype = '之前续订失败的已过期订阅已成功续订';
                    break;
                case 'BILLING_RETRY':
                    element.subtype = '订阅已过期，因为订阅在计费重试期结束之前未能续订';
                    break;
                case 'GRACE_PERIOD':
                    element.subtype = '由于账单问题，订阅续订失败';
                    break;
                case 'UPGRADE':
                    element.subtype = '用户已升级其订阅或跨级升级到具有相同期限的订阅';
                    break;
                case 'VOLUNTARY':
                    element.subtype = '用户禁用订阅自动续订后订阅已过期';
                    break;
                case 'DOWNGRADE':
                    element.subtype = '用户已降级其订阅或跨级订阅，降级将在下一个续订日期生效。';
                    break;
                case 'RESUBSCRIBE':
                    element.subtype = '用户重新订阅';
                    break;
            }

            // 处理 status
            switch (element.status) {
                case '1':
                    element.status = '自动续订已激活';
                    break;
                case '2':
                    element.status = '自动续订订阅已过期';
                    break;
                case '3':
                    element.status = '自动续订订阅处于计费重试期';
                    break;
                case '4':
                    element.status = '自动续订订阅处于计费宽限期';
                    break;
                case '5':
                    element.status = '自动续订订阅已被撤销';
                    break;
            }

            // 处理 offer_discount_type
            if (element.offer_discount_type == 'FREE_TRIAL') {
                element.offer_discount_type = '免费试用';
            }

            // 处理 consumption_request_reasons
            switch (element.consumption_request_reason) {
                case 'UNINTENDED_PURCHASE':
                    element.consumption_request_reason = '客户无意进行应用内购买。';
                    break;
                case 'FULFILLMENT_ISSUE':
                    element.consumption_request_reason = '客户在接收或使用应用内购买时遇到了问题。';
                    break;
                case 'UNSATISFIED_WITH_PURCHASE':
                    element.consumption_request_reason = '客户对应用内购买不满意。';
                    break;
                case 'LEGAL':
                    element.consumption_request_reason = '客户基于法律原因要求退款。';
                    break;
                case 'OTHER':
                    element.consumption_request_reason = '客户因其他原因要求退款。';
                    break;
            }

            // 处理 revocation_reason
            switch (element.revocation_reason) {
                case '0':
                    element.revocation_reason = 'App Store 因其他原因（例如意外购买）代表客户退还了交易款项。';
                    break;
                case '1':
                    element.revocation_reason = '由于您的应用中存在实际问题或感知问题,App Store 代表客户退还了交易款项。';
                    break;
            }

            // 处理 auto_renew_status
            switch (element.auto_renew_status) {
                case '0':
                    element.auto_renew_status = '关闭';
                    break;
                case '1':
                    element.auto_renew_status = '开启';
                    break;
            }

            // 处理 expiration_intent
            switch (element.expiration_intent) {
                case '1':
                    element.expiration_intent = '用户取消了订阅';
                    break;
                case '2':
                    element.expiration_intent = '由于计费问题取消，如信用卡到期或支付失败。';
                    break;
                case '3':
                    element.expiration_intent = '用户从App Store Connect主动取消了订阅。';
                    break;
                case '4':
                    element.expiration_intent = '产品不可用时续订失败';
                    break;
            }
            element.create_date = moment(element.create_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.purchase_date = moment(element.purchase_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.recent_subscription_start_date = moment(element.recent_subscription_start_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.renewal_date = moment(element.renewal_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.original_purchase_date = moment(element.original_purchase_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });


        // 一旦我们处理完所有元素，返回结果给客户端
        res.send(utils.returnData({
            data: result
        }));
    } catch (err) {
        res.send(utils.returnData({ code: -1, err, req }));
    }
});

//获取国家列表
router.post("/getStorefrontList", async (req, res) => {
    const sql = `SELECT DISTINCT storefront FROM js_app_apple_notifications;`;

    try {
        // 获取去重后的 storefront 列表
        const result = await new Promise((resolve, reject) => {
            pool.query(sql, (err, results) => {
                if (err) return reject(err);
                resolve(results);
            });
        });

        // 数据处理，将结果转换成指定的格式
        const data = result.map((row, index) => ({
            id: index + 1,
            name: row.storefront
        }));

        // 将结果发送给客户端
        res.send({
            code: 0,
            msg: "操作成功",
            data: data
        });

    } catch (err) {
        res.send({
            code: -1,
            msg: "操作失败",
            error: err.message
        });
    }
});

router.post('/test', async (req, res) => {
    let data = req.body
    const headers = {
        'Content-Type': 'application/json',
        'Authorization': 'Basic OjAwMDA=',
        'User-Agent': 'Universal/0 CFNetwork/1410.1 Darwin/22.6.0',
        'Accept': '*/*',
        'Accept-Language': 'zh-CN,zh-Hans;q=0.9',
        'Accept-Encoding': 'gzip, deflate',
        'Connection': 'keep-alive'
    };

    axios.post('http://192.168.5.21/sony/accessControl', data, { headers: headers })
        .then(response => {
            console.log('Response:', response.data);
        })
        .catch(error => {
            console.error('Error:', error);
        });
})
async function mysqlQuery(sql) {
    return new Promise((resolve, reject) => {
        pool.query(sql, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
// 从 x5c 中提取公钥
function getKeyFromX5C(x5c) {
    // 将第一个证书从 x5c 中取出并转换为 PEM 格式
    const pemCert = `-----BEGIN CERTIFICATE-----\n${x5c[0]}\n-----END CERTIFICATE-----`;
    // 从 PEM 证书生成公钥
    const publicKey = crypto.createPublicKey(pemCert).export({ type: 'spki', format: 'pem' });
    return publicKey;
}
// 从 x5c 中提取Kid
function getKeyFromX5CKid(x5c) {
    // 将第一个证书从 x5c 中取出并转换为 PEM 格式
    const pemCert1 = `-----BEGIN CERTIFICATE-----\n${x5c[0]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash1 = crypto.createHash('sha256');
    certHash1.update(pemCert1);
    const certFingerprint1 = certHash1.digest('hex');
    const pemCert2 = `-----BEGIN CERTIFICATE-----\n${x5c[1]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash2 = crypto.createHash('sha256');
    certHash2.update(pemCert2);
    const certFingerprint2 = certHash2.digest('hex');
    const pemCert3 = `-----BEGIN CERTIFICATE-----\n${x5c[2]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash3 = crypto.createHash('sha256');
    certHash3.update(pemCert3);
    const certFingerprint3 = certHash3.digest('hex');

    // 将指纹作为 kid 返回
    // return certFingerprint;
}
function getYesterdayMidnightTimestamp() {
    // 获取当前时间的Date对象
    const now = new Date();

    // 昨天的日期：获取年、月、日，然后设置为昨天
    const yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 74);

    // 返回昨天0点的毫秒级时间戳
    return yesterday.getTime();
}

router.post('/getHistory', async (req, res) => {
    try {
        const jwtToken = createJWT('com.seamobitech.HeartPulse');
        let hasMore = true;
        let paginationToken = null;
        const allNotifications = [];

        while (hasMore) {
            const query = {
                startDate: getYesterdayMidnightTimestamp(),
                endDate: Date.now(),
                // onlyFailures: true,
            };

            let requery = `https://api.storekit.itunes.apple.com/inApps/v1/notifications/history`
            // 有条件地添加 paginationToken 到请求体
            if (paginationToken) {
                requery = `https://api.storekit.itunes.apple.com/inApps/v1/notifications/history?paginationToken=${paginationToken}`;
            }
            const response = await axios.post(requery, query, {
                headers: {
                    Authorization: `Bearer ${jwtToken}`,
                    'Content-Type': 'application/json',
                }
            });

            const { notificationHistory, paginationToken: newPaginationToken, hasMore: newHasMore } = response.data;
            allNotifications.push(...notificationHistory);

            paginationToken = newPaginationToken;
            hasMore = newHasMore;
            // console.log(response.data.notificationHistory.length)
        }
        console.log('打完收工')
        console.log(allNotifications.length);
        // res.send({ data: allNotifications });
        if (allNotifications.length > 0) {
            allNotifications.forEach(item => {
                //处理 signedPayload
                handleMissedNotifications(item.signedPayload)
            })
        }

    } catch (error) {
        console.error('Error fetching notification history:', error);
        res.status(500).send({ error: 'Failed to fetch notification history' });
    }
})
//处理 signedPayload

async function handleMissedNotifications(signedPayload) {
    const decodedToken = jwt.decode(signedPayload, { complete: true });
    if (!decodedToken) {
        console.log('Invalid token')
        return
    }
    const decodedHeader = decodedToken.header;
    // const response = await axios.get('https://appleid.apple.com/auth/keys');
    if (!decodedHeader || !decodedHeader.x5c) {
        // console.error('JWT Header:', decodedHeader);
        console.log('Invalid token: missing x5c')
        return
    }
    // 从 x5c 提取公钥
    const publicKey = getKeyFromX5C(decodedHeader.x5c);
    getKeyFromX5CKid(decodedHeader.x5c)
    // 验证 JWT
    jwt.verify(signedPayload, publicKey, { algorithms: ['ES256'] }, (err, decoded) => {
        if (err) {
            console.error('JWT verification error:', err);
            return
        }

        // 如果解码成功，处理通知
        handleMissedNotification(decoded, publicKey);
    });
}

router.post('/apple-notifications', async (req, res) => {
    const { signedPayload } = req.body;
    // 解码 JWT 以获取头部信息
    const decodedToken = jwt.decode(signedPayload, { complete: true });
    if (!decodedToken) {
        return res.status(400).json({ status: 'error', message: 'Invalid token' });
    }
    const decodedHeader = decodedToken.header;
    // const response = await axios.get('https://appleid.apple.com/auth/keys');
    if (!decodedHeader || !decodedHeader.x5c) {
        // console.error('JWT Header:', decodedHeader);
        return res.status(400).json({ status: 'error', message: 'Invalid token: missing x5c' });
    }
    // 从 x5c 提取公钥 
    const publicKey = getKeyFromX5C(decodedHeader.x5c);
    getKeyFromX5CKid(decodedHeader.x5c)
    // 验证 JWT
    jwt.verify(signedPayload, publicKey, { algorithms: ['ES256'] }, (err, decoded) => {
        if (err) {
            console.error('JWT verification error:', err);
            return res.status(400).json({ status: 'error', message: err.message });
        }

        // 如果解码成功，处理通知
        handleNotification(decoded, publicKey);
        res.json({ status: 'success' });
    });
});


// 示例：处理漏发的通知

async function handleMissedNotification(notification, publicKey) {
    const rows = await mysqlQuery(
        `SELECT *
FROM js_app_apple_notifications
WHERE notification_UUID = ?`,
        [notification.notificationUUID]
    );
    if (rows.length > 0) {
        console.log('有重复的', notification.notificationUUID)
    } else {
        // 没有重复条目，继续后续操作
        console.log('没有重复的', notification.notificationUUID)
    }



}

async function handleNotification(notification, publicKey) {
    const notificationType = notification.notificationType;

    const jsonPayload = notification.data;

    let notificationBox = {}
    notificationBox.id = utils.createId();
    notificationBox.notification_type = notification.notificationType;
    notificationBox.subtype = notification.subtype;
    notificationBox.notification_UUID = notification.notificationUUID;
    notificationBox.signed_date = moment(notification.signedDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
    notificationBox.pk = jsonPayload.bundleId;
    notificationBox.consumption_request_reason = jsonPayload.consumptionRequestReason;
    notificationBox.status = jsonPayload.status;
    notificationBox.environment = jsonPayload.environment;
    notificationBox.create_date = utils.formatDate(new Date());
    notificationBox.update_date = utils.formatDate(new Date());

    // 主要字段容器
    let expires = null;
    let purchaseDate = null;
    let isTrialPeriod = false;
    let productId = null;
    let original_transaction_id = null;
    let transactionId = null;

    // 解码和验证signedTransactionInfo
    if (jsonPayload.signedTransactionInfo) {
        jwt.verify(jsonPayload.signedTransactionInfo, publicKey, { algorithms: ['ES256'] }, (err, decodedTransaction) => {
            if (err) {
                console.error('Transaction JWT verification error:', err);
            } else {
                // console.log('Decoded Transaction Info:', decodedTransaction);
                // 处理交易信息
                notificationBox.transaction_id = decodedTransaction.transactionId;
                notificationBox.app_account_token = decodedTransaction.appAccountToken;
                notificationBox.currency = decodedTransaction.currency;
                notificationBox.expires_date = moment(decodedTransaction.expiresDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                expires = Number(decodedTransaction.expiresDate || 0);
                purchaseDate = Number(decodedTransaction.purchaseDate || 0);
                productId = decodedTransaction.productId;
                original_transaction_id = decodedTransaction.originalTransactionId;
                transactionId = decodedTransaction.transactionId;

                notificationBox.in_app_ownership_type = decodedTransaction.inAppOwnershipType;
                notificationBox.is_upgraded = decodedTransaction.isUpgraded;
                notificationBox.offer_discount_type = decodedTransaction.offerDiscountType;
                notificationBox.offer_type = decodedTransaction.offerType;
                notificationBox.original_purchase_date = moment(decodedTransaction.originalPurchaseDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                notificationBox.original_transaction_id = decodedTransaction.originalTransactionId;
                notificationBox.price = utils.convertToDollars(decodedTransaction.price);
                notificationBox.product_id = decodedTransaction.productId;
                notificationBox.purchase_date = moment(decodedTransaction.purchaseDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                notificationBox.quantity = decodedTransaction.quantity;
                notificationBox.revocation_date = moment(decodedTransaction.revocationDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                notificationBox.revocation_reason = decodedTransaction.revocationReason;
                notificationBox.storefront = decodedTransaction.storefront;
                notificationBox.storefront_id = decodedTransaction.storefrontId;
                notificationBox.subscription_groupIdentifier = decodedTransaction.subscriptionGroupIdentifier;
                notificationBox.transaction_reason = decodedTransaction.transactionReason;
                notificationBox.type = decodedTransaction.type;
                notificationBox.web_order_lineItem_id = decodedTransaction.webOrderLineItemId;
            }
        });
    }

    // 解码和验证signedRenewalInfo
    if (jsonPayload.signedRenewalInfo) {
        jwt.verify(jsonPayload.signedRenewalInfo, publicKey, { algorithms: ['ES256'] }, (err, decodedRenewal) => {
            if (err) {
                console.error('Renewal JWT verification error:', err);
            } else {
                // console.log('Decoded Renewal Info:', decodedRenewal);
                // 处理续订信息
                notificationBox.auto_renew_product_id = decodedRenewal.autoRenewProductId;
                notificationBox.auto_renew_status = decodedRenewal.autoRenewStatus;
                notificationBox.expiration_intent = decodedRenewal.expirationIntent;
                notificationBox.grace_period_expires_date = moment(decodedRenewal.gracePeriodExpiresDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                notificationBox.is_in_billing_retry_period = decodedRenewal.isInBillingRetryPeriod;
                notificationBox.recent_subscription_start_date = moment(decodedRenewal.recentSubscriptionStartDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                notificationBox.renewal_date = moment(decodedRenewal.renewalDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                notificationBox.renewal_price = utils.convertToDollars(decodedRenewal.renewalPrice);
            }
        });
    }

    if (notificationBox.environment == 'Sandbox') {
        notificationBox.state = 1
    }
    const { text, values } = utils.createInsertSql(
        "js_app_apple_notifications",
        notificationBox
    );

    if (
        notificationBox.pk === 'com.trans.translationAiAndSubtitle' ||
        notificationBox.pk === 'com.TranslationAISubTitle'
    ) {
        console.log('收到momo订阅通知')
        let result = await utils.queryMOMO(text, values);
    } else {
        let result = await utils.query(text, values);
    }

    // console.log(result)

    if (notificationType == 'CONSUMPTION_REQUEST') {
        const transactionId = notificationBox.transaction_id;
        await sendConsumptionInfo(notificationBox); // 回应苹果

        // 选择数据库操作函数
        let dbQueryFunc;
        if (
            notificationBox.pk === 'com.trans.translationAiAndSubtitle' ||
            notificationBox.pk === 'com.TranslationAISubTitle'
        ) {
            dbQueryFunc = mysqlQueryMOMO;
        } else {
            dbQueryFunc = mysqlQuery;
        }

        // 查询数据库，检查是否存在重复记录
        const checkQuery = `
            SELECT is_agree 
            FROM apple_refund_notifications
            WHERE transaction_id = ?
        `;
        let existingRecords = await dbQueryFunc(checkQuery, [transactionId]);

        if (existingRecords.length === 0) {
            // 插入新记录
            const { text, values } = utils.createInsertSql("apple_refund_notifications", notificationBox);
            // 插入语句通常还是对主DB，你可按需选择是 utils.query 还是 utils.queryMOMO
            // 如果也要区分数据库，建议utils.query和utils.queryMOMO一致
            let result2;
            if (
                notificationBox.pk === 'com.trans.translationAiAndSubtitle' ||
                notificationBox.pk === 'com.TranslationAISubTitle'
            ) {
                result2 = await utils.queryMOMO(text, values);
            } else {
                result2 = await utils.query(text, values);
            }
        } else if (existingRecords.length > 0) {
            // 更新现有记录的 update_date，假设 is_agree 不等于 1
            const updateQuery = `
                UPDATE apple_refund_notifications 
                    SET update_date = ?, create_date = ?, is_agree = 0
                WHERE transaction_id = ?
            `;
            await dbQueryFunc(updateQuery, [utils.formatDate(new Date()), utils.formatDate(new Date()), transactionId]);
            console.log('Updated existing record with new update_date');
        }
    }

    if ((notificationType === 'REFUND' || notificationType === 'REFUND_DECLINED')) {
        // 判断不同包名决定用哪个查询方法
        let dbQueryFunc;
        if (
            notificationBox.pk === 'com.trans.translationAiAndSubtitle' ||
            notificationBox.pk === 'com.TranslationAISubTitle'
        ) {
            dbQueryFunc = mysqlQueryMOMO;
        } else {
            dbQueryFunc = mysqlQuery;
        }

        // 查询数据库，检查是否存在 transaction_id 的记录
        const checkQuery = `
            SELECT id 
            FROM apple_refund_notifications 
            WHERE transaction_id = ?
        `;
        let existingRecords = await dbQueryFunc(checkQuery, [notificationBox.transaction_id]);

        if (existingRecords.length > 0) {
            let isAgreeValue;

            if (notificationType === 'REFUND') {
                isAgreeValue = 2;
            } else if (notificationType === 'REFUND_DECLINED') {
                isAgreeValue = 1;
            }
            // 获取第一条记录的 ID
            const recordId = existingRecords[0].id;
            // 更新记录的 is_agree 字段
            const updateQuery = `
                UPDATE apple_refund_notifications 
                SET is_agree = ?, update_date = ?
                WHERE id = ?
            `;
            await dbQueryFunc(updateQuery, [isAgreeValue, utils.formatDate(new Date()), recordId]);
            console.log(`Updated transaction_id ${notificationBox.transaction_id} with is_agree = ${isAgreeValue}`);
        } else {
            console.log(`Transaction_id ${notificationBox.transaction_id} not found in table.`);
        }
    }


    // ---- 主体处理区 ----
    if (notificationBox.pk === 'com.trans.translationAiAndSubtitle' || notificationBox.pk === 'com.TranslationAISubTitle') {
        if (notificationType == 'ONE_TIME_CHARGE') return
        const chinaTime = new Date();

        // --- 1. 查找会员用户 ---
        const userResult = await mysqlQueryMOMO(
            'SELECT * FROM transir_users WHERE original_transaction_id = ? LIMIT 1',
            [notificationBox.original_transaction_id]
        );
        if (!userResult || userResult.length === 0) {
            // 新订单回调提前到达，还没注册用户，不更新主表，只做日志或通知记录
            // 可以落一张通知快照表，便于异常补发
            return;
        }
        const user = userResult[0];

        // --- 2. 收集订单和会员关键时间字段 ---
        const nowTs = Date.now() + 30 * 60 * 1000; // 当前时间戳
        // 通知里的会员到期时间戳
        const notify_expiry_ts = expires || 0;
        // 用户当前会员到期时间戳
        const db_expiry_ts = user.subscription_expiry_date
            ? moment.tz(user.subscription_expiry_date, "Asia/Shanghai").valueOf()
            : 0;
        // 订单的本期购买时间
        const notify_purchase_date_str =
            notificationBox.purchase_date && notificationBox.purchase_date !== "Invalid date"
                ? notificationBox.purchase_date
                : moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"); // 兜底

        // ==== 读取配额配置 ====
        // ==========================
        // ↓↓↓ 新增部分：查询更多资源配额 ↓↓↓
        // ==========================
        const [config] = await mysqlQueryMOMO(
            `SELECT 
            regular_feature_minutes, 
            regular_photos_limit, 
            regular_tokens_limit,          
            regular_float_copy_limit,     
            regular_quick_text_limit      
         FROM transir_system_configs LIMIT 1`
        );
        // ==========================
        // ↑↑↑ 新增部分 ↑↑↑
        // ==========================
        if (!config) {
            console.error('系统配额配置表缺失');
            return;
        }

        // 【A】会员周期推进：只更新身份与到期时间，不动流量reset字段、不重置配额
        if (notify_expiry_ts > db_expiry_ts) {
            // 会员新一期来了：只推进会员状态！！
            await mysqlQueryMOMO(
                `UPDATE transir_users SET
                user_type = 'renewal_member',
                subscription_start_date = ?,  
                subscription_expiry_date = ?, 
                current_product_id = ?,
                transaction_id = ?,
                original_transaction_id = ?
            WHERE user_id = ?`,
                [
                    notify_purchase_date_str,
                    moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"),
                    notificationBox.product_id || null,
                    notificationBox.transaction_id || null,
                    notificationBox.original_transaction_id || null,
                    user.user_id
                ]
            );
            console.log(`[会员订单推进] 用户${user.user_id} 新到期: ${moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")}----${chinaTime.toLocaleString()} `);
            return;
        }
        console.log(notify_expiry_ts, 'notify_expiry_ts')
        console.log(nowTs, 'nowTs')
        console.log(notify_expiry_ts <= nowTs, 'notify_expiry_ts <= nowTs')
        console.log(user.user_type != 'regular', "user.user_type != 'regular'")
        // 【B】会员到期降级逻辑：会员过期且主表不是普通用户时，降级&重置普通配额
        if (notify_expiry_ts <= nowTs && user.user_type != 'regular') {
            // ==========================
            // ↓↓↓ 新增部分：检查待生效的临时会员天数 ↓↓↓
            // ==========================
            const postVipPendingDays = user.post_vip_pending_days || 0;
            console.log(`[会员过期检查] 用户${user.user_id} 待生效临时会员天数: ${postVipPendingDays}----${chinaTime.toLocaleString()}`);

            if (postVipPendingDays > 0) {
                // 有待生效的临时会员天数，自动续上临时会员
                const memberConf = await getMemberConfig(); // 获取会员配额配置
                const currentTimeStr = moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                const tempMemberExpiry = moment(currentTimeStr).add(postVipPendingDays, 'days').format("YYYY-MM-DD HH:mm:ss");
                // 关键：判断天数大于等于30天，用自然月
                let nextUsageResetDate;
                if (postVipPendingDays >= 30) {
                    // 1个月或以上，next_usage_reset_date = 本次激活日+1自然月
                    nextUsageResetDate = moment(currentTimeStr).add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
                } else {
                    // 小于1个月，直接等于此次到期日
                    nextUsageResetDate = tempMemberExpiry;
                }
                await mysqlQueryMOMO(
                    `UPDATE transir_users SET
                        user_type = 'temporary_member',
                        subscription_start_date = ?,
                        subscription_expiry_date = ?,
                        last_usage_reset_date = ?,
                        usage_base_date = ?,
                        next_usage_reset_date = ?,
                        feature_minutes = ?,
                        feature_minutes_left = ?,
                        photos = ?,
                        photos_left = ?,
                        tokens = ?,
                        tokens_left = ?,
                        float_copy = ?,
                        float_copy_left = ?,
                        quick_text = ?,
                        quick_text_left = ?,
                        post_vip_pending_days = 0
                     WHERE user_id = ?`,
                    [
                        currentTimeStr,
                        tempMemberExpiry,
                        currentTimeStr,
                        currentTimeStr,
                        nextUsageResetDate,
                        memberConf.feature_minutes,
                        memberConf.feature_minutes,
                        memberConf.photos,
                        memberConf.photos,
                        memberConf.tokens,
                        memberConf.tokens,
                        memberConf.float_copy,
                        memberConf.float_copy,
                        memberConf.quick_text,
                        memberConf.quick_text,
                        user.user_id
                    ]
                );
                console.log(`[会员过期自动续期] 用户${user.user_id} 自动续期为临时会员${postVipPendingDays}天，到期时间: ${tempMemberExpiry}----${chinaTime.toLocaleString()}`);
                return;
            }
            // ==========================
            // ↑↑↑ 新增部分结束 ↑↑↑
            // ==========================




            // ==========================
            // ↓↓↓ 新增/修改部分 ↓↓↓
            // 1. 兼容新增加的配额相关字段
            // 2. SQL同步支持新资源
            // ==========================
            // 新加：tokens/float_copy/quick_text 的重置变量
            const feature_minutes = config.regular_feature_minutes;
            const photos = config.regular_photos_limit;
            const tokens = config.regular_tokens_limit;     // 新增
            const float_copy = config.regular_float_copy_limit; // 新增
            const quick_text = config.regular_quick_text_limit; // 新增
            const currentTimeStr = moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            const nextResetDate = moment(currentTimeStr).add(1, 'month').format("YYYY-MM-DD HH:mm:ss");

            const regular_photos_limit = config.regular_photos_limit || 0;
            const regular_photos_used = user.regular_photos_used || 0;
            const photos_left = Math.max(0, regular_photos_limit - regular_photos_used);

            await mysqlQueryMOMO(
                `UPDATE transir_users SET
                    user_type = 'regular',
                    subscription_expiry_date = NULL,
                    feature_minutes = ?,
                    feature_minutes_used = 0,
                    feature_minutes_left = ?,
                    photos = ?,
                    photos_used= ?,
                    photos_left  = ?,
                    tokens = ?,
                    tokens_used = 0,
                    tokens_left = ?,
                    float_copy = ?,
                    float_copy_used = 0,
                    float_copy_left = ?,
                    quick_text = ?,
                    quick_text_used = 0,
                    quick_text_left = ?,
                    last_usage_reset_date = ?,
                    next_usage_reset_date = ?
                 WHERE user_id = ?`,
                [
                    feature_minutes,            // 1
                    feature_minutes,            // 2
                    regular_photos_limit,    // 照片总配额（普通）
                    regular_photos_used,     // 普通已用照片数 (regular_photos_used 字段)              // 3
                    photos_left,
                    tokens,                     // 4
                    tokens,                     // 5
                    float_copy,                 // 6
                    float_copy,                 // 7
                    quick_text,                 // 8
                    quick_text,                 // 9
                    currentTimeStr,             // 10
                    nextResetDate,              // 11
                    user.user_id                // 12
                ]
            );
            console.log(`[会员过期降级] 用户${user.user_id} 已降级为普通用户----${chinaTime.toLocaleString()}`);
            return;
        }

        // 【C】旧通知，不处理，只记录日志即可
        console.log(`[忽略历史通知] 用户${user.user_id}：通知到期:${notify_expiry_ts}, 当前DB到期:${db_expiry_ts}----${chinaTime.toLocaleString()}`);
    }

    // ---- 主体处理区 ----
    if (
        notificationBox.pk === 'com.finger.AIRecipes' ||
        notificationBox.pk === 'com.tcrecipeai.testApp'
    ) {
        const chinaTime = new Date();

        // --- 1. 查找会员用户 ---
        const userResult = await mysqlQuery(
            'SELECT * FROM Recipe_user WHERE original_transaction_id = ? LIMIT 1',
            [notificationBox.original_transaction_id]
        );
        if (!userResult || userResult.length === 0) {
            // 没注册用户，不更新主表，只记日志
            return;
        }
        const user = userResult[0];

        // --- 2. 收集订单和会员关键时间字段 ---
        const nowTs = Date.now();
        const notify_expiry_ts = expires || 0;
        const db_expiry_ts = user.subscription_expiry_date
            ? moment.tz(user.subscription_expiry_date, "Asia/Shanghai").valueOf()
            : 0;
        const notify_purchase_date_str =
            notificationBox.purchase_date && notificationBox.purchase_date !== "Invalid date"
                ? notificationBox.purchase_date
                : moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"); // 兜底

        // 【A】会员周期推进：只更新身份与到期时间
        if (notify_expiry_ts > db_expiry_ts) {
            await mysqlQuery(
                `UPDATE Recipe_user SET
                user_type = 'renewal_member',
                subscription_start_date = ?,
                subscription_expiry_date = ?,
                current_product_id = ?,
                transaction_id = ?,
                original_transaction_id = ?
            WHERE user_id = ?`,
                [
                    notify_purchase_date_str,
                    moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"),
                    notificationBox.product_id || null,
                    notificationBox.transaction_id || null,
                    notificationBox.original_transaction_id || null,
                    user.user_id
                ]
            );
            console.log(`[会员订单推进] 用户${user.user_id} 新到期: ${moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")}----${chinaTime.toLocaleString()}`);
            return;
        }

        // 【B】会员到期降级逻辑：会员过期且主表不是普通用户时，降级
        if (notify_expiry_ts <= nowTs && user.user_type !== 'regular') {
            await mysqlQuery(
                `UPDATE Recipe_user SET
                user_type = 'regular',
                subscription_expiry_date = NULL
            WHERE user_id = ?`,
                [user.user_id]
            );
            console.log(`[会员过期降级] 用户${user.user_id} 已降级为普通用户----${chinaTime.toLocaleString()}`);
            return;
        }

        // 【C】旧通知，不处理，只记录日志即可
        console.log(`[忽略历史通知] 用户${user.user_id}：通知到期:${notify_expiry_ts}, 当前DB到期:${db_expiry_ts}----${chinaTime.toLocaleString()}`);
    }
    // ---- 主体处理区 ----
    if (
        notificationBox.pk === 'com.withYou.TestApp' ||
        notificationBox.pk === 'com.finger.withYou'
    ) {
        const chinaTime = new Date();

        // --- 1. 查找会员用户 ---
        const userResult = await mysqlQueryTarot(
            'SELECT * FROM tarot_users WHERE original_transaction_id = ? LIMIT 1',
            [notificationBox.original_transaction_id]
        );
        if (!userResult || userResult.length === 0) {
            // 没注册用户，不更新主表，只记日志
            return;
        }
        const user = userResult[0];

        // --- 2. 收集订单和会员关键时间字段 ---
        const nowTs = Date.now();
        const notify_expiry_ts = expires || 0;
        const db_expiry_ts = user.subscription_expiry_date
            ? moment.tz(user.subscription_expiry_date, "Asia/Shanghai").valueOf()
            : 0;
        const notify_purchase_date_str =
            notificationBox.purchase_date && notificationBox.purchase_date !== "Invalid date"
                ? notificationBox.purchase_date
                : moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"); // 兜底

        // 【A】会员周期推进：只更新身份与到期时间
        if (notify_expiry_ts > db_expiry_ts) {
            await mysqlQueryTarot(
                `UPDATE tarot_users SET
                user_type = 'renewal_member',
                subscription_start_date = ?,
                subscription_expiry_date = ?,
                current_product_id = ?,
                transaction_id = ?,
                original_transaction_id = ?
            WHERE user_id = ?`,
                [
                    notify_purchase_date_str,
                    moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"),
                    notificationBox.product_id || null,
                    notificationBox.transaction_id || null,
                    notificationBox.original_transaction_id || null,
                    user.user_id
                ]
            );
            console.log(`[会员订单推进] 用户${user.user_id} 新到期: ${moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")}----${chinaTime.toLocaleString()}`);
            return;
        }
        // 【B】会员到期降级逻辑：会员过期且主表不是普通用户时，降级
        if (notify_expiry_ts <= nowTs && user.user_type !== 'regular') {
            // ==========================
            // ↓↓↓ 新增部分：检查待生效的临时会员天数 ↓↓↓
            // ==========================
            const postVipPendingDays = user.post_vip_pending_days || 0;
            console.log(`[会员过期检查] 用户${user.user_id} 待生效临时会员天数: ${postVipPendingDays}----${chinaTime.toLocaleString()}`);

            if (postVipPendingDays > 0) {
                // 有待生效的临时会员天数，自动续上临时会员
                const currentTimeStr = moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                const tempMemberExpiry = moment(currentTimeStr).add(postVipPendingDays, 'days').format("YYYY-MM-DD HH:mm:ss");
                // 关键：判断天数大于等于30天，用自然月
                let nextUsageResetDate;
                if (postVipPendingDays >=33333333) {
                    // 1个月或以上，next_usage_reset_date = 本次激活日+1自然月
                    nextUsageResetDate = moment(currentTimeStr).add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
                } else {
                    // 小于1个月，直接等于此次到期日
                    nextUsageResetDate = tempMemberExpiry;
                }
                await mysqlQueryTarot(
                    `UPDATE tarot_users SET
                        user_type = 'temporary_member',
                        subscription_start_date = ?,
                        subscription_expiry_date = ?,
                        post_vip_pending_days = 0
                     WHERE user_id = ?`,
                    [
                        currentTimeStr,
                        tempMemberExpiry,
                        user.user_id
                    ]
                );
                console.log(`[会员过期自动续期] 用户${user.user_id} 自动续期为临时会员${postVipPendingDays}天，到期时间: ${tempMemberExpiry}----${chinaTime.toLocaleString()}`);
                return;
            }
            // ==========================
            // ↑↑↑ 新增部分结束 ↑↑↑
            // ==========================







            await mysqlQueryTarot(
                `UPDATE tarot_users SET
                user_type = 'regular',
                subscription_expiry_date = NULL
            WHERE user_id = ?`,
                [user.user_id]
            );
            console.log(`[会员过期降级] 用户${user.user_id} 已降级为普通用户----${chinaTime.toLocaleString()}`);
            return;
        }

        // 【C】旧通知，不处理，只记录日志即可
        console.log(`[忽略历史通知] 用户${user.user_id}：通知到期:${notify_expiry_ts}, 当前DB到期:${db_expiry_ts}----${chinaTime.toLocaleString()}`);
    }
    if (
        notificationBox.pk === 'com.zhijian.Museo' ||
        notificationBox.pk === 'com.seamobitech.Museo'
    ) {
        const chinaTime = new Date();

        // --- 1. 查找会员用户 ---
        const userResult = await mysqlQueryMuseo(
            'SELECT * FROM museo_users WHERE original_transaction_id = ? LIMIT 1',
            [notificationBox.original_transaction_id]
        );
        if (!userResult || userResult.length === 0) {
            // 没注册用户，不更新主表，只记日志
            return;
        }
        const user = userResult[0];

        // --- 2. 收集订单和会员关键时间字段 ---
        const nowTs = Date.now();
        const notify_expiry_ts = expires || 0;
        const db_expiry_ts = user.subscription_expiry_date
            ? moment.tz(user.subscription_expiry_date, "Asia/Shanghai").valueOf()
            : 0;
        const notify_purchase_date_str =
            notificationBox.purchase_date && notificationBox.purchase_date !== "Invalid date"
                ? notificationBox.purchase_date
                : moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"); // 兜底

        // 【A】会员周期推进：只更新身份与到期时间
        if (notify_expiry_ts > db_expiry_ts) {
            await mysqlQueryTarot(
                `UPDATE museo_users SET
                user_type = 'renewal_member',
                subscription_start_date = ?,
                subscription_expiry_date = ?,
                current_product_id = ?,
                transaction_id = ?,
                original_transaction_id = ?
            WHERE user_id = ?`,
                [
                    notify_purchase_date_str,
                    moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"),
                    notificationBox.product_id || null,
                    notificationBox.transaction_id || null,
                    notificationBox.original_transaction_id || null,
                    user.user_id
                ]
            );
            console.log(`[会员订单推进] 用户${user.user_id} 新到期: ${moment.tz(new Date(notify_expiry_ts), "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")}----${chinaTime.toLocaleString()}`);
            return;
        }

        // 【B】会员到期降级逻辑：会员过期且主表不是普通用户时，降级
        if (notify_expiry_ts <= nowTs && user.user_type !== 'regular') {
            // ==========================
            // ↓↓↓ 新增部分：检查待生效的临时会员天数 ↓↓↓
            // ==========================
            const postVipPendingDays = user.post_vip_pending_days || 0;
            console.log(`[会员过期检查] 用户${user.user_id} 待生效临时会员天数: ${postVipPendingDays}----${chinaTime.toLocaleString()}`);

            if (postVipPendingDays > 0) {
                // 有待生效的临时会员天数，自动续上临时会员
                 
                const currentTimeStr = moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                const tempMemberExpiry = moment(currentTimeStr).add(postVipPendingDays, 'days').format("YYYY-MM-DD HH:mm:ss");
                // 关键：判断天数大于等于30天，用自然月
                let nextUsageResetDate;
                if (postVipPendingDays >= 33333333) {
                    // 1个月或以上，next_usage_reset_date = 本次激活日+1自然月
                    nextUsageResetDate = moment(currentTimeStr).add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
                } else {
                    // 小于1个月，直接等于此次到期日
                    nextUsageResetDate = tempMemberExpiry;
                }
                await mysqlQueryMuseo(
                    `UPDATE museo_users SET
                        user_type = 'temporary_member',
                        subscription_start_date = ?,
                        subscription_expiry_date = ?,
                        post_vip_pending_days = 0
                     WHERE user_id = ?`,
                    [
                        currentTimeStr,
                        tempMemberExpiry,
                        user.user_id
                    ]
                );
                console.log(`[会员过期自动续期] 用户${user.user_id} 自动续期为临时会员${postVipPendingDays}天，到期时间: ${tempMemberExpiry}----${chinaTime.toLocaleString()}`);
                return;
            }
            // ==========================
            // ↑↑↑ 新增部分结束 ↑↑↑
            // ==========================







            await mysqlQueryTarot(
                `UPDATE museo_users SET
                user_type = 'regular',
                subscription_expiry_date = NULL
            WHERE user_id = ?`,
                [user.user_id]
            );
            console.log(`[会员过期降级] 用户${user.user_id} 已降级为普通用户----${chinaTime.toLocaleString()}`);
            return;
        }

        // 【C】旧通知，不处理，只记录日志即可
        console.log(`[忽略历史通知] 用户${user.user_id}：通知到期:${notify_expiry_ts}, 当前DB到期:${db_expiry_ts}----${chinaTime.toLocaleString()}`);
    }
}
async function getMemberConfig() {
    const configSql = 'SELECT * FROM transir_system_configs LIMIT 1';
    const configs = await mysqlQueryMOMO(configSql, []);
    if (configs.length === 0) return {
        feature_minutes: 0,
        photos: 0
    };
    return {
        feature_minutes: configs[0].member_feature_minutes || 0,
        photos: configs[0].member_photos_limit || 0,
        tokens: configs[0].member_tokens_limit || 0,
        float_copy: configs[0].member_float_copy_limit || 0,
        quick_text: configs[0].member_quick_text_limit || 0
    };
}
// async function mysqlQueryMOMO(sql, arr) {
//     return new Promise((resolve, reject) => {
//         poolmomo.query(sql, arr, (error, results, fields) => {
//             if (error) {
//                 return reject(error);
//             }
//             resolve(results);
//         });
//     });
// }
async function mysqlQueryMOMO(sql, arr) {
    return new Promise((resolve, reject) => {
        poolmomo.getConnection((err, conn) => {
            if (err) return reject(err);
            conn.query(sql, arr, (error, results) => {
                conn.release();
                if (error) return reject(error);
                resolve(results);
            });
        });
    });
}
async function mysqlQueryTarot(sql, arr) {
    return new Promise((resolve, reject) => {
        poolTarot.getConnection((err, conn) => {
            if (err) return reject(err);
            conn.query(sql, arr, (error, results) => {
                conn.release();
                if (error) return reject(error);
                resolve(results);
            });
        });
    });
}
async function mysqlQueryMuseo(sql, arr) {
    return new Promise((resolve, reject) => {
        poolmuseo.getConnection((err, conn) => {
            if (err) return reject(err);
            conn.query(sql, arr, (error, results) => {
                conn.release();
                if (error) return reject(error);
                resolve(results);
            });
        });
    });
}
// async function sendConsumptionInfo(content) {
//     const jwtToken = createJWT(content.pk);
//     try {
//         const consumptionUrl = `https://api.storekit.itunes.apple.com/inApps/v1/transactions/consumption/${content.transaction_id}`;



//         const ConsumptionRequest = {
//             accountTenure: 0,
//             appAccountToken: '',
//             consumptionStatus: 3,
//             customerConsented: true,
//             deliveryStatus: 0,
//             lifetimeDollarsPurchased: 0,
//             lifetimeDollarsRefunded: 0,
//             platform: 1,
//             playTime: 0,
//             refundPreference: 2,
//             sampleContentProvided: true,
//             userStatus: 0
//         };
//         const config = {
//             headers: {
//                 Authorization: `Bearer ${jwtToken}`,
//                 'Content-Type': 'application/json',
//             },
//         };

//         const response = await axios.put(consumptionUrl, ConsumptionRequest, config);
//         console.log('退款回应成功:-----------------' + response.status + '----------' + content.transaction_id);
//     } catch (error) {
//         console.error('发送消耗信息时出错:', error.response?.data || error.message);
//     }
// }

// 创建 JWT
function createJWT(pk) {
    //原始订单id是串联整个用户所有订单
    //transaction_id是交易id 可以用做用户退款  查看用户这笔订单申请退款 也用来可以看后续这个订单是同意还是拒绝
    let privateKey = null
    let keyId = null
    let teamId = null
    if (pk == 'com.seamobitech.roku') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/iosRoku.p8');
        keyId = 'KPTK8YW54N';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.UniversalTVRemoteControl') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/universal.p8');
        keyId = 'NDVH93RZ93';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.whatsscanwebclone') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/waai.p8');
        keyId = 'M9HSW38PQ7';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.QRCodeReadAndGenerate') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/qr.p8');
        keyId = 'BPK84L5D9P';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.BeautyAI') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/beauty.p8');
        keyId = 'YKSHCJXDZ3';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.HeartPulse') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/iosHeartPulse.p8');
        keyId = '33R7CA35M3';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.zhijian.remoteControl') {
        // SubscriptionKey_UH45FLL4DM
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/universalRemote2.p8');
        keyId = 'UH45FLL4DM';
        teamId = '701e51de-a934-4e08-96e1-5513f30f0d04';
    }
    if (pk == 'com.trans.translationAiAndSubtitle') {
        privateKey = fs.readFileSync('/home/wwwroot/default/iosTransir.p8');
        keyId = '47KPS4C6NW';
        teamId = '701e51de-a934-4e08-96e1-5513f30f0d04';
    }
    const now = Math.floor(Date.now() / 1000);
    const expiresIn = 60 * 60;

    const payload = {
        iss: teamId,
        iat: now,
        exp: now + expiresIn,
        aud: 'appstoreconnect-v1',
        bid: pk
    };
    const token = jwt.sign(payload, privateKey, {
        algorithm: 'ES256',
        keyid: keyId
    });

    return token;
}
router.post('/tokenTest', async (req, res) => {

    await sendConsumptionInfo(req.body)
})
async function testNotificationToken(token) {
    const jwtToken = createJWT('com.seamobitech.roku');
    try {
        const response = await axios.post('https://api.storekit.itunes.apple.com/inApps/v1/notifications/test', {}, {
            headers: {
                Authorization: `Bearer ${jwtToken}`,
                'Content-Type': 'application/json',
            }
        });

        const testNotificationToken = response.data.testNotificationToken;
        console.log('Received test notification token:', testNotificationToken);
        return testNotificationToken;
    } catch (error) {
        console.log(error)
        console.error('Error getting test notification token:', error.response ? error.response.data : error.message);
    }
}




function processResult(result) {
    return new Promise((resolve) => {
        result.forEach((element) => {
            element.create_date = moment(element.create_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.update_date = moment(element.update_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.signed_date = moment(element.signed_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.expires_date = moment(element.expires_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.original_purchase_date = moment(element.original_purchase_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.purchase_date = moment(element.purchase_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.revocation_date = moment(element.revocation_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.grace_period_expires_date = moment(element.grace_period_expires_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.recent_subscription_start_date = moment(element.recent_subscription_start_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            element.renewal_date = moment(element.renewal_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");


        });

        resolve();
    });
};
//每天成功续订
router.post('/calculateDailySuccessfulPayments', async (req, res) => {
    const { pk, dateRange } = req.body;
    let sql = `SELECT * FROM successful_renewals WHERE pk = ?`;
    let params = [pk];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    try {
        const results = await mysqlQuery(sql, params);
        const formattedData = results
            .filter(row => moment(row.date).format('MM-DD') !== '09-06') // 过滤掉9月6日的数据
            .map(row => ({
                date: moment(row.date).format('MM-DD'), // 提取并格式化日期
                count: row.count // 直接使用 count
            }));

        res.send(
            utils.returnData({
                data: formattedData
            })
        );
    } catch (error) {
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
    // const { pk, dateRange } = req.body;
    // let sql = `SELECT * FROM js_app_apple_notifications WHERE pk = ?`;
    // let params = [pk];

    // if (dateRange && dateRange.start && dateRange.end) {
    //     const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
    //     const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

    //     sql += ` AND update_date BETWEEN ? AND ?`;
    //     params.push(startDate, endDate);
    // }
    // try {
    //     const results = await mysqlQuery(sql, params);
    //     const dailyCounts = {};

    //     results.forEach(row => {
    //         const date = moment(row.update_date).format('MM-DD');
    //         if (date === '09-06') {
    //             return; // 跳过9月6日的数据
    //         }
    //         if (!dailyCounts[date]) {
    //             dailyCounts[date] = 0;
    //         }
    //         if (row.notification_type === 'DID_RENEW') {
    //             dailyCounts[date]++;
    //         }
    //     });

    //     const formattedData = Object.keys(dailyCounts).map(date => ({
    //         date,
    //         count: dailyCounts[date]
    //     }));
    //     res.send(
    //         utils.returnData({
    //             data: formattedData
    //         })
    //     );
    // } catch (error) {
    //     res.status(500).json({
    //         success: false,
    //         message: 'Internal Server Error'
    //     });
    // }
});
//每天成功退款
router.post('/calculateDailySuccessfulRefunds', async (req, res) => {
    const { pk, dateRange } = req.body;
    let sql = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ?`;
    let params = [pk];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND base_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        // 执行数据库查询
        const results = await mysqlQuery(sql, params);
        const dailyCounts = {};

        // 遍历结果来计算退款的数量
        const formattedData = results
            .filter(row => moment(row.base_date).format('MM-DD') !== '09-06') // 过滤掉9月6日的数据
            .map(row => ({
                date: moment(row.base_date).format('MM-DD'), // 提取并格式化日期
                count: row.daily_refund_count // 直接使用 count
            }));

        // 返回成功的数据响应
        res.send(
            utils.returnData({
                data: formattedData
            })
        );
    } catch (error) {
        // 捕获错误并返回 500 状态码和错误信息
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
    // const { pk, dateRange } = req.body;
    // let sql = `SELECT * FROM js_app_apple_notifications WHERE pk = ?`;
    // let params = [pk];

    // if (dateRange && dateRange.start && dateRange.end) {
    //     const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
    //     const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

    //     sql += ` AND update_date BETWEEN ? AND ?`;
    //     params.push(startDate, endDate);
    // }

    // try {
    //     // 执行数据库查询
    //     const results = await mysqlQuery(sql, params);
    //     const dailyCounts = {};

    //     // 遍历结果来计算退款的数量
    //     results.forEach(row => {
    //         const date = moment(row.update_date).format('MM-DD'); // 格式化日期
    //         if (date === '09-06') {
    //             return; // 跳过9月6日的数据
    //         }
    //         if (!dailyCounts[date]) {
    //             dailyCounts[date] = 0; // 初始化日期对应的计数
    //         }
    //         // 判断通知类型是否为退款成功 REFUND
    //         if (row.notification_type === 'REFUND') {
    //             dailyCounts[date]++;  // 每找到一个 REFUND 通知，计数加 1
    //         }
    //     });

    //     // 格式化最终数据
    //     const formattedData = Object.keys(dailyCounts).map(date => ({
    //         date,
    //         count: dailyCounts[date]
    //     }));

    //     // 返回成功的数据响应
    //     res.send(
    //         utils.returnData({
    //             data: formattedData
    //         })
    //     );
    // } catch (error) {
    //     // 捕获错误并返回 500 状态码和错误信息
    //     res.status(500).json({
    //         success: false,
    //         message: 'Internal Server Error'
    //     });
    // }
});

//转正率
router.post('/calculateConversionRate', async (req, res) => {
    const { pk, dateRange } = req.body;
    let sqlInitialBuy = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ?`;
    let paramsInitialBuy = [pk];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).subtract(3, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).subtract(3, 'days').endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlInitialBuy += ` AND base_date BETWEEN ? AND ?`;
        paramsInitialBuy.push(startDate, endDate);
    }

    try {

        const initialBuyResults = await mysqlQuery(sqlInitialBuy, paramsInitialBuy);
        const formattedData = initialBuyResults
            .filter(row =>
                moment(row.base_date).format('MM-DD') !== '09-06' && // 过滤掉9月6日的数据
                (row.converted_after_trial !== null && row.converted_after_trial > 0) // 排除converted_after_trial没有值或等于0的情况
            )
            .map(row => ({
                date: moment(row.base_date).format('MM-DD'), // 提取并格式化日期
                initialCount: row.daily_trial_count,
                renewalCount: row.converted_after_trial,
                conversionRate: ((row.converted_after_trial / row.daily_trial_count) * 100).toFixed(2),
            }));

        // 返回成功的数据响应
        res.send(
            utils.returnData({
                data: formattedData
            })
        );





    } catch (error) {
        console.error(error);
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
    // const { pk, dateRange } = req.body;
    // let sqlInitialBuy = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND subtype = 'INITIAL_BUY'`;
    // let paramsInitialBuy = [pk];

    // if (dateRange && dateRange.start && dateRange.end) {
    //     const startDate = moment(dateRange.start).subtract(3, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
    //     const endDate = moment(dateRange.end).subtract(3, 'days').endOf('day').format('YYYY-MM-DD HH:mm:ss');

    //     sqlInitialBuy += ` AND update_date BETWEEN ? AND ?`;
    //     paramsInitialBuy.push(startDate, endDate);
    // }

    // try {
    //     const initialBuyResults = await mysqlQuery(sqlInitialBuy, paramsInitialBuy);
    //     const initialBuyCounts = {};
    //     const initialBuyUserIds = {};

    //     initialBuyResults.forEach(row => {
    //         const date = moment(row.update_date).add(3, 'days').format('YYYY-MM-DD');
    //         if (!initialBuyCounts[date]) {
    //             initialBuyCounts[date] = 0;
    //             initialBuyUserIds[date] = new Set();
    //         }
    //         initialBuyCounts[date]++;
    //         initialBuyUserIds[date].add(row.user_id); // 假设每个记录都有 user_id 字段
    //     });



    //     const renewalCounts = {};
    //     const renewalPromises = Object.keys(initialBuyCounts).map(async date => {
    //         const startDate = moment(date).startOf('day').format('YYYY-MM-DD HH:mm:ss');
    //         const endDate = moment(date).endOf('day').format('YYYY-MM-DD HH:mm:ss');

    //         const sqlRenewal = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND notification_type = 'DID_RENEW' AND update_date BETWEEN ? AND ? AND DATE(original_purchase_date) = DATE(?)`;
    //         const paramsRenewal = [pk, startDate, endDate, moment(date).subtract(3, 'days').format('YYYY-MM-DD')];

    //         const renewalResults = await mysqlQuery(sqlRenewal, paramsRenewal);



    //         renewalCounts[date] = renewalResults.filter(row => initialBuyUserIds[date].has(row.user_id)).length;
    //     });

    //     await Promise.all(renewalPromises);
    //     let conversionRates = Object.keys(initialBuyCounts).map(date => {
    //         const initialCount = initialBuyCounts[date];
    //         const renewalCount = renewalCounts[date] || 0;
    //         const conversionRate = (renewalCount / initialCount) * 100;
    //         const formattedDate = moment(date).subtract(3, 'days').format('MM-DD');
    //         return {
    //             date: formattedDate,
    //             initialCount,
    //             renewalCount,
    //             conversionRate: conversionRate.toFixed(2) // 保留两位小数
    //         };
    //     });
    //     conversionRates = conversionRates.filter(rate => rate.date !== '09-06');
    //     res.json({
    //         success: true,
    //         data: conversionRates
    //     });
    // } catch (error) {
    //     console.error(error);
    //     res.status(500).json({
    //         success: false,
    //         message: 'Internal Server Error'
    //     });
    // }
});

router.post('/updateConvertedAfterTrial', async (req, res) => {
    const { pk } = req.body;
    let sqlFetchRates = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ? AND (converted_after_trial IS NULL OR converted_after_trial = '')`;
    let paramsFetchRates = [pk];

    try {
        const ratesResults = await mysqlQuery(sqlFetchRates, paramsFetchRates);

        for (const rate of ratesResults) {
            const baseDate = moment(rate.base_date).format('YYYY-MM-DD');
            const startDate = moment(baseDate).subtract(3, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
            const endDate = moment(baseDate).subtract(3, 'days').endOf('day').format('YYYY-MM-DD HH:mm:ss');

            // Fetch INITIAL_BUY records within the calculated date range
            let sqlInitialBuy = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND subtype = 'INITIAL_BUY' AND update_date BETWEEN ? AND ?`;
            let paramsInitialBuy = [pk, startDate, endDate];

            const initialBuyResults = await mysqlQuery(sqlInitialBuy, paramsInitialBuy);
            const initialBuyCounts = {};
            const initialBuyUserIds = {};

            initialBuyResults.forEach(row => {
                const date = moment(row.update_date).add(3, 'days').format('YYYY-MM-DD');
                if (!initialBuyCounts[date]) {
                    initialBuyCounts[date] = 0;
                    initialBuyUserIds[date] = new Set();
                }
                initialBuyCounts[date]++;
                initialBuyUserIds[date].add(row.user_id);
            });

            const renewalCounts = {};
            const renewalPromises = Object.keys(initialBuyCounts).map(async date => {
                const startDate = moment(date).startOf('day').format('YYYY-MM-DD HH:mm:ss');
                const endDate = moment(date).endOf('day').format('YYYY-MM-DD HH:mm:ss');

                const sqlRenewal = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND notification_type = 'DID_RENEW' AND update_date BETWEEN ? AND ? AND DATE(original_purchase_date) = DATE(?)`;
                const paramsRenewal = [pk, startDate, endDate, moment(date).subtract(3, 'days').format('YYYY-MM-DD')];

                const renewalResults = await mysqlQuery(sqlRenewal, paramsRenewal);
                renewalCounts[date] = renewalResults.filter(row => initialBuyUserIds[date].has(row.user_id)).length;
            });

            await Promise.all(renewalPromises);

            // Calculate total renewal count after the trial period
            const totalRenewalCount = Object.keys(initialBuyCounts).reduce((sum, date) => {
                return sum + (renewalCounts[date] || 0);
            }, 0);

            const updateBaseDate = moment(baseDate).subtract(3, 'days').format('YYYY-MM-DD');
            const updateSql = `UPDATE new_weekly_renewal_rates SET converted_after_trial = ? WHERE base_date = ? AND pk = ?`;
            await mysqlQuery(updateSql, [totalRenewalCount, updateBaseDate, pk]);
        }

        res.json({
            success: true,
            message: 'Converted after trial updated successfully'
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
});







router.post('/calculateDailyDirectPurchases', async (req, res) => {
    const { pk, dateRange } = req.body;
    let sql = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND subtype = 'INITIAL_BUY' AND (offer_discount_type IS NULL OR offer_discount_type != 'FREE_TRIAL')`;
    let params = [pk];
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND update_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        // 执行数据库查询
        const results = await mysqlQuery(sql, params);
        const dailyCounts = {};

        // 初始化日期范围内的所有日期计数为0
        const start = moment(dateRange.start).startOf('day');
        const end = moment(dateRange.end).endOf('day');
        for (let m = start; m.isBefore(end); m.add(1, 'days')) {
            const date = m.format('MM-DD');
            dailyCounts[date] = 0;
        }

        // 遍历结果来计算直接购买的数量
        results.forEach(row => {
            const date = moment(row.update_date).format('MM-DD'); // 格式化日期
            // 判断通知类型是否为直接购买 INITIAL_BUY 并且 offer_discount_type 不等于 FREE_TRIAL
            if (dailyCounts[date] !== undefined) {
                dailyCounts[date]++;  // 每找到一个符合条件的通知，计数加 1
            }
        });

        // 格式化最终数据
        const formattedData = Object.keys(dailyCounts).map(date => ({
            date,
            count: dailyCounts[date]
        }));

        // 返回成功的数据响应
        res.send(
            utils.returnData({
                data: formattedData
            })
        );
    } catch (error) {
        // 捕获错误并返回 500 状态码和错误信息
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
});
//试用人数
router.post('/calculateInitialBuysWithFreeTrial', async (req, res) => {
    const { pk, dateRange } = req.body;
    let sql = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND subtype = 'INITIAL_BUY' AND offer_discount_type = 'FREE_TRIAL'`;
    let params = [pk];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND update_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        const results = await mysqlQuery(sql, params);
        const dailyCounts = {};

        results.forEach(row => {
            const date = moment(row.update_date).format('MM-DD');
            if (date === '09-06') {
                return; // 跳过9月6日的数据
            }
            if (!dailyCounts[date]) {
                dailyCounts[date] = 0;
            }
            dailyCounts[date]++;
        });

        const formattedData = Object.keys(dailyCounts).map(date => ({
            date,
            count: dailyCounts[date]
        }));

        res.send(
            utils.returnData({
                data: formattedData
            })
        );
    } catch (error) {
        console.error(error);
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
});

//新增人数
router.post('/calculateDailyNewUsers', async (req, res) => {
    const { pk, dateRange } = req.body;
    let sql = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ? `;
    let params = [pk];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND base_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        const results = await mysqlQuery(sql, params);
        const dailyCounts = {};

        results.forEach(row => {
            const date = moment(row.base_date).format('MM-DD');
            if (date === '09-06') {
                return; // 跳过9月6日的数据
            }
            if (!dailyCounts[date]) {
                dailyCounts[date] = 0;
            }
            dailyCounts[date]++;
            row.base_date = date
        });
        const formattedData = Object.keys(dailyCounts).map(date => ({
            date,
            count: dailyCounts[date]
        }));

        res.send(
            utils.returnData({
                data: results
            })
        );
    } catch (error) {
        console.error(error);
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
});
//试用率
router.post('/calculateTrialConversionRate', async (req, res) => {
    const { pk, dateRange } = req.body;
    let sql = `SELECT base_date, daily_trial_count, daily_new_count FROM new_weekly_renewal_rates WHERE pk = ? `;
    let params = [pk];

    // 如果提供了日期范围，增加日期过滤条件
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND base_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        const results = await mysqlQuery(sql, params);

        // 存储每日数据的数组
        const formattedData = results.map(row => {
            const date = moment(row.base_date).format('MM-DD');  // 当前日期
            const trialCount = row.daily_trial_count || 0;  // 试用人数
            const newCount = row.daily_new_count || 0;  // 新增人数

            // 计算试用率，避免除以0的情况
            const conversionRate = newCount > 0 ? ((trialCount / newCount) * 100).toFixed(2) : 0;

            return {
                date,                // 当前日期
                trialCount,          // 试用人数
                newCount,            // 新增人数
                conversionRate       // 试用率
            };
        });

        // 返回结果
        res.send(
            utils.returnData({
                data: formattedData
            })
        );
    } catch (error) {
        console.error('Error while calculating trial conversion rate:', error);
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
});


router.post('/insertSuccessfulRenewals', async (req, res) => {
    const { pk } = req.body;

    const startTime = new Date();
    // console.log('Start Time:', startTime.toLocaleString());


    const sqlSelect = `
        SELECT 
            update_date, original_transaction_id, transaction_id
        FROM 
            js_app_apple_notifications
        WHERE 
            notification_type = 'DID_RENEW' AND
            pk = ?
    `;

    const paramsSelect = [pk];

    try {
        const results = await mysqlQuery(sqlSelect, paramsSelect);

        const groupedByDate = results.reduce((acc, row) => {
            const dateObj = new Date(row.update_date);
            const date = dateObj.toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');
            if (!acc[date]) {
                acc[date] = [];
            }
            acc[date].push(row.original_transaction_id);
            return acc;
        }, {});

        const today = new Date().toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');

        for (const [date, originalTransactionIds] of Object.entries(groupedByDate)) {
            if (date === today) {
                // console.log(`Skipping today's data for date: ${date}`);
                continue;
            }

            // console.log(`Date: ${date}, OriginalTransactionIds Length: ${originalTransactionIds.length}`);

            const sqlCheck = `
                SELECT 1 FROM successful_renewals WHERE date = ? AND pk = ?
            `;
            const paramsCheck = [date, pk];
            const checkResult = await mysqlQuery(sqlCheck, paramsCheck);
            if (checkResult.length === 0) {
                const sqlInsert = `
                    INSERT INTO successful_renewals (
                        id,
                        date, 
                        originalOrderIds, 
                        pk,
                        count,
                        create_date,
                        update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsert = [
                    utils.createId(),
                    date,
                    originalTransactionIds.join(','),
                    pk,
                    originalTransactionIds.length,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];

                await mysqlQuery(sqlInsert, paramsInsert);
                // console.log(`Inserted data for date: ${date}`);
            }
        }

        // console.log('Data inserted successfully');
    } catch (error) {
        console.error('Error inserting data:', error);
    } finally {
        const endTime = new Date();
        // console.log('End Time:', endTime.toLocaleString());
    }
});

router.post('/calculateAndStoreWeeklyRenewalRates', async (req, res) => {
    const startTime = new Date();
    // console.log('开始时间:', startTime.toLocaleString());

    try {
        const { pk } = req.body;
        // console.log(`处理 PK: ${pk}`);

        // 获取所有日期
        const sqlSelectAllDates = `
            SELECT DISTINCT date
            FROM successful_renewals
            WHERE pk = ?
            ORDER BY date
        `;
        const paramsAllDates = [pk];
        const allDatesResults = await mysqlQuery(sqlSelectAllDates, paramsAllDates);
        const allDates = allDatesResults.map(row => row.date);
        // console.log(`找到 ${allDates.length} 个基准日期。`);

        for (const baseDate of allDates) {
            // console.log(`处理基准日期: ${baseDate}`);

            const sqlSelectBaseWeek = `
                SELECT originalOrderIds
                FROM successful_renewals
                WHERE date = ? AND pk = ?
            `;
            const paramsBaseWeek = [baseDate, pk];
            const baseWeekResults = await mysqlQuery(sqlSelectBaseWeek, paramsBaseWeek);

            // 解析 originalOrderIds
            const baseWeekIds = new Set();
            baseWeekResults.forEach(row => {
                const ids = row.originalOrderIds.split(',');
                ids.forEach(id => baseWeekIds.add(id.trim()));
            });

            const initialCount = baseWeekIds.size;
            // console.log(`基准日期 ${baseDate} 的初始人数: ${initialCount}`);

            let currentDate = new Date(baseDate);
            const weeklyRates = [];

            while (true) {
                // 移动到下一周的同一天
                currentDate.setDate(currentDate.getDate() + 7);
                const nextWeekDate = currentDate.toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');

                const sqlSelectNextWeek = `
                    SELECT originalOrderIds
                    FROM successful_renewals
                    WHERE date = ? AND pk = ?
                `;
                const paramsNextWeek = [nextWeekDate, pk];
                const nextWeekResults = await mysqlQuery(sqlSelectNextWeek, paramsNextWeek);

                if (nextWeekResults.length === 0) {
                    // console.log(`没有数据用于周开始于 ${nextWeekDate}。结束循环。`);
                    break; // 没有更多数据
                }

                const nextWeekIds = new Set();
                nextWeekResults.forEach(row => {
                    const ids = row.originalOrderIds.split(',');
                    ids.forEach(id => nextWeekIds.add(id.trim()));
                });

                const continuedRenewals = [...baseWeekIds].filter(id => nextWeekIds.has(id));

                const renewalRate = continuedRenewals.length / initialCount;
                const renewalCount = continuedRenewals.length;
                weeklyRates.push({ week: nextWeekDate, rate: renewalRate, count: renewalCount });

                // console.log(`基准日期: ${baseDate}, 周: ${nextWeekDate}, 续费率: ${renewalRate.toFixed(2)}, 续费人数: ${renewalCount}`);
            }

            // 检查记录是否存在
            const sqlCheckExistence = `
                SELECT COUNT(*) AS count
                FROM weekly_renewal_rates
                WHERE base_date = ? AND pk = ?
            `;
            const formattedBaseDate = formatDateToLocal(baseDate);
            const paramsCheckExistence = [formattedBaseDate, pk];
            const result = await mysqlQuery(sqlCheckExistence, paramsCheckExistence);

            if (result[0].count > 0) {
                // 更新记录
                const sqlUpdateRate = `
                    UPDATE weekly_renewal_rates
                    SET initial_count = ?, rates = ?, update_date = ?
                    WHERE base_date = ? AND pk = ?
                `;
                const paramsUpdateRate = [
                    initialCount,
                    JSON.stringify(weeklyRates),
                    utils.formatDate(new Date()),
                    formattedBaseDate,
                    pk
                ];
                await mysqlQuery(sqlUpdateRate, paramsUpdateRate);
                // console.log(`更新数据成功，基准日期: ${baseDate}`);
            } else {
                // 插入新记录
                const sqlInsertRate = `
                    INSERT INTO weekly_renewal_rates (
                        id, base_date, initial_count, rates, pk, create_date, update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsertRate = [
                    utils.createId(),
                    formattedBaseDate,
                    initialCount,
                    JSON.stringify(weeklyRates),
                    pk,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];
                await mysqlQuery(sqlInsertRate, paramsInsertRate);
                // console.log(`插入数据成功，基准日期: ${baseDate}`);
            }
        }
        res.status(200).send('周续费率计算并存储成功。');
    } catch (error) {
        console.error('处理请求时出错:', error);
        res.status(500).send('处理请求时发生错误。');
    } finally {
        const endTime = new Date();
        // console.log('结束时间:', endTime.toLocaleString());
    }
});
function formatDateToLocal(date) {
    const localDate = new Date(date);
    localDate.setMinutes(localDate.getMinutes() - localDate.getTimezoneOffset());
    return localDate.toISOString().split('T')[0];
}

router.post('/getWeeklyRenewalRatesByPk', async (req, res) => {
    try {
        const { pk } = req.body;

        const sqlSelectRatesByPk = `
            SELECT *
            FROM weekly_renewal_rates
            WHERE pk = ?
        `;
        const params = [pk];
        const ratesResults = await mysqlQuery(sqlSelectRatesByPk, params);

        // Remove the first item from the results
        const modifiedResults = ratesResults.slice(1);

        res.send(
            utils.returnData({
                data: modifiedResults
            })
        );
    } catch (error) {
        console.error('获取数据时出错:', error);
        res.status(500).send('获取数据时发生错误。');
    }
});

//获取周续订率列表new
router.post('/getWeeklyRenewalRatesByPkNew', async (req, res) => {
    try {
        const { pk, dateRange } = req.body;

        let sqlSelectRatesByPk = `
            SELECT *
            FROM new_weekly_renewal_rates
            WHERE pk = ?
        `;
        let params = [pk];
        if (dateRange && dateRange.start && dateRange.end) {
            const startDate = dateRange.start;
            const endDate = dateRange.end;
            sqlSelectRatesByPk += ` AND base_date BETWEEN ? AND ?`;
            params.push(startDate, endDate);
        }
        const ratesResults = await mysqlQuery(sqlSelectRatesByPk, params);

        // Convert the first item's base_date to China timezone and compare
        const firstItem = ratesResults[0];
        if (firstItem) {
            const baseDate = new Date(firstItem.base_date);
            const chinaTime = new Intl.DateTimeFormat('zh-CN', {
                timeZone: 'Asia/Shanghai',
                year: 'numeric',
                month: '2-digit',
                day: '2-digit'
            }).format(baseDate);
            // console.log(chinaTime)
            if (chinaTime === '2024/09/06') {
                ratesResults.shift(); // Remove the first item if the date matches
            }
        }

        res.send(
            utils.returnData({
                data: ratesResults
            })
        );
    } catch (error) {
        console.error('获取数据时出错:', error);
        res.status(500).send('获取数据时发生错误。');
    }
});



router.post('/insertNewUserRenewals', async (req, res) => {
    const { pk } = req.body;
    const startTime = new Date();

    const sqlSelect = `
        SELECT 
            original_purchase_date, update_date, original_transaction_id, transaction_id
        FROM 
            js_app_apple_notifications
        WHERE 
            notification_type = 'DID_RENEW' AND
            pk = ?
    `;

    const paramsSelect = [pk];

    try {
        const results = await mysqlQuery(sqlSelect, paramsSelect);

        const filteredResults = results.filter(row => {
            const originalDate = moment(row.original_purchase_date).tz('Asia/Shanghai').format('YYYY-MM-DD');
            const updateDate = moment(row.update_date).tz('Asia/Shanghai').subtract(3, 'days').format('YYYY-MM-DD');
            return originalDate === updateDate;
        });

        const groupedByDate = filteredResults.reduce((acc, row) => {
            const date = moment(row.update_date).tz('Asia/Shanghai').format('YYYY-MM-DD');
            if (!acc[date]) {
                acc[date] = [];
            }
            acc[date].push(row.original_transaction_id);
            return acc;
        }, {});

        const today = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');

        for (const [date, originalTransactionIds] of Object.entries(groupedByDate)) {
            if (date === today) {
                // console.log(`Skipping today's data for date: ${date}`);
                continue;
            }

            // console.log(`Date: ${date}, OriginalTransactionIds Length: ${originalTransactionIds.length}`);

            const sqlCheck = `
                SELECT 1 FROM new_successful_renewals WHERE date = ? AND pk = ?
            `;
            const paramsCheck = [date, pk];
            const checkResult = await mysqlQuery(sqlCheck, paramsCheck);
            if (checkResult.length === 0) {
                const sqlInsert = `
                    INSERT INTO new_successful_renewals (
                        id,
                        date, 
                        originalOrderIds, 
                        pk,
                        count,
                        create_date,
                        update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsert = [
                    utils.createId(),
                    date,
                    originalTransactionIds.join(','),
                    pk,
                    originalTransactionIds.length,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];

                await mysqlQuery(sqlInsert, paramsInsert);
                // console.log(`Inserted data for date: ${date}`);
            }
        }

        // console.log('Data inserted successfully');
    } catch (error) {
        console.error('Error inserting data:', error);
    } finally {
        const endTime = new Date();
        // console.log('End Time:', endTime.toLocaleString());
        calculateAndStoreWeeklyRenewalRatesNew(pk)
    }

});
router.post('/calculateAndStoreWeeklyRenewalRatesNew', async (req, res) => {
    const { pk } = req.body;

    // console.log('走第二个接口')
    const startTime = new Date();
    // console.log('开始时间:', startTime.toLocaleString());

    try {
        // console.log(`处理 PK: ${pk}`);

        // 获取所有日期
        const sqlSelectAllDates = `
            SELECT DISTINCT date
            FROM new_successful_renewals
            WHERE pk = ?
            ORDER BY date
        `;
        const paramsAllDates = [pk];
        const allDatesResults = await mysqlQuery(sqlSelectAllDates, paramsAllDates);
        const allDates = allDatesResults.map(row => row.date);
        // console.log(`找到 ${allDates.length} 个基准日期。`);

        for (const baseDate of allDates) {
            // console.log(`处理基准日期: ${baseDate}`);

            const sqlSelectBaseWeek = `
                SELECT originalOrderIds
                FROM new_successful_renewals
                WHERE date = ? AND pk = ?
            `;
            const paramsBaseWeek = [baseDate, pk];
            const baseWeekResults = await mysqlQuery(sqlSelectBaseWeek, paramsBaseWeek);

            // 解析 originalOrderIds
            const baseWeekIds = new Set();
            baseWeekResults.forEach(row => {
                const ids = row.originalOrderIds.split(',');
                ids.forEach(id => baseWeekIds.add(id.trim()));
            });

            const initialCount = baseWeekIds.size;
            // console.log(`基准日期 ${baseDate} 的初始人数: ${initialCount}`);

            let currentDate = new Date(baseDate);
            const weeklyRates = [];

            while (true) {
                // 移动到下一周的同一天
                currentDate.setDate(currentDate.getDate() + 7);
                const nextWeekDate = currentDate.toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');

                const sqlSelectNextWeek = `
                    SELECT originalOrderIds
                    FROM successful_renewals
                    WHERE date = ? AND pk = ?
                `;
                const paramsNextWeek = [nextWeekDate, pk];
                const nextWeekResults = await mysqlQuery(sqlSelectNextWeek, paramsNextWeek);

                if (nextWeekResults.length === 0) {
                    // console.log(`没有数据用于周开始于 ${nextWeekDate}。结束循环。`);
                    break; // 没有更多数据
                }

                const nextWeekIds = new Set();
                nextWeekResults.forEach(row => {
                    const ids = row.originalOrderIds.split(',');
                    ids.forEach(id => nextWeekIds.add(id.trim()));
                });

                const continuedRenewals = [...baseWeekIds].filter(id => nextWeekIds.has(id));

                const renewalRate = continuedRenewals.length / initialCount;
                const renewalCount = continuedRenewals.length;
                weeklyRates.push({ week: nextWeekDate, rate: renewalRate, count: renewalCount });

                // console.log(`基准日期: ${baseDate}, 周: ${nextWeekDate}, 续费率: ${renewalRate.toFixed(2)}, 续费人数: ${renewalCount}`);
            }

            // 检查记录是否存在
            const sqlCheckExistence = `
                SELECT COUNT(*) AS count
                FROM new_weekly_renewal_rates
                WHERE base_date = ? AND pk = ?
            `;
            const formattedBaseDate = formatDateToLocal(baseDate);
            const paramsCheckExistence = [formattedBaseDate, pk];
            const result = await mysqlQuery(sqlCheckExistence, paramsCheckExistence);

            if (result[0].count > 0) {
                // 更新记录
                const sqlUpdateRate = `
                    UPDATE new_weekly_renewal_rates
                    SET initial_count = ?, rates = ?, update_date = ?
                    WHERE base_date = ? AND pk = ?
                `;
                const paramsUpdateRate = [
                    initialCount,
                    JSON.stringify(weeklyRates),
                    utils.formatDate(new Date()),
                    formattedBaseDate,
                    pk
                ];
                await mysqlQuery(sqlUpdateRate, paramsUpdateRate);
                // console.log(`更新数据成功，基准日期: ${baseDate}`);
            } else {
                // 插入新记录
                const sqlInsertRate = `
                    INSERT INTO new_weekly_renewal_rates (
                        id, base_date, initial_count, rates, pk, create_date, update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsertRate = [
                    utils.createId(),
                    formattedBaseDate,
                    initialCount,
                    JSON.stringify(weeklyRates),
                    pk,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];
                await mysqlQuery(sqlInsertRate, paramsInsertRate);
                // console.log(`插入数据成功，基准日期: ${baseDate}`);
            }
        }
    } catch (error) {
        console.error('处理请求时出错:', error);
    } finally {
        const endTime = new Date();
        // console.log('结束时间:', endTime.toLocaleString());
    }
});
//查询当天新用户3天后的订阅率
router.post("/newUserSubscriptionRateThreeDaysLater", async (req, res) => {
    const { pk, dateRange } = req.body;
    let sql = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ?`;
    let params = [pk];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD');

        sql += ` AND base_date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        const results = await mysqlQuery(sql, params);
        const formattedData = await Promise.all(results.map(async row => {
            let currentBaseDate = row.base_date;

            // 计算三天后的基准日期
            const targetDate = moment(currentBaseDate).add(3, 'days').format('YYYY-MM-DD');

            // 查询三天后的人数
            const sqlSelectFutureCount = `
                SELECT initial_count 
                FROM new_weekly_renewal_rates 
                WHERE base_date = ? AND pk = ?
            `;
            const paramsSelectFutureCount = [targetDate, pk];
            const futureCountResults = await mysqlQuery(sqlSelectFutureCount, paramsSelectFutureCount);

            const newSubs3Days = futureCountResults.length > 0 ? futureCountResults[0].initial_count : 0;
            // 计算订阅转化率
            const conversionRate = row.daily_new_count > 0
                ? (newSubs3Days / row.daily_new_count) * 100
                : 0;
            return {
                date: moment(currentBaseDate).format('MM-DD'),  // 当前日期,
                daily_new_count: row.daily_new_count,
                daily_trial_count: row.daily_trial_count,
                newSubs3Days,
                conversion_rate: conversionRate.toFixed(2)  // 保留两位小数
            };
        }));

        res.send(
            utils.returnData({
                data: formattedData
            })
        );
    } catch (error) {
        console.error(error);
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
});

//获取历史退款记录

router.post('/getRefundList', async (req, res) => {
    const { pk, tid } = req.body
    const jwtToken = createJWT(pk);
    try {
        const consumptionUrl = `https://api.storekit.itunes.apple.com/inApps/v2/refund/lookup/${tid}`;
        const config = {
            headers: {
                Authorization: `Bearer ${jwtToken}`,
                'Content-Type': 'application/json',
            },
        };

        const response = await axios.get(consumptionUrl, config);
        console.log(response)
    } catch (error) {
        console.error('发送消耗信息时出错:', error.response?.data || error.message);
    }

})

router.post('/testx', async (req, res) => {
    console.log('进入接口')
    const { tid } = req.body
    const jwtToken = createJWT('com.seamobitech.whatsscanwebclone');
    console.log(jwtToken)
    try {
        const consumptionUrl = `https://api.storekit.itunes.apple.com/inApps/v1/subscriptions/${tid}`;

        const config = {
            headers: {
                Authorization: `Bearer ${jwtToken}`,
                'Content-Type': 'application/json',
            },
        };

        const response = await axios.get(consumptionUrl, config);
        console.log(response.data)
        const transaction = response.data.data[0].lastTransactions[0];
        const decodedToken = jwt.decode(transaction.signedTransactionInfo, { complete: true });
        res.send({
            data: response.data,
            transaction: transaction
        })
        // console.log('JWT header:', decodedToken.header); // 查看 alg、kid
        //  let signedRenewalInfo = ''
        // let signedTransactionInfo = ''

        // const decodedHeader = decodedToken.header;
        // // 从 x5c 提取公钥
        // const publicKey = getKeyFromX5C(decodedHeader.x5c);
        // getKeyFromX5CKid(decodedHeader.x5c)
        // // 验证 JWT
        // jwt.verify(transaction.signedTransactionInfo, publicKey, { algorithms: ['ES256'] }, (err, decoded) => {
        //     console.log(decoded, 'decoded')
        //     signedTransactionInfo = decoded
        // });
        // jwt.verify(transaction.signedRenewalInfo, publicKey, { algorithms: ['ES256'] }, (err, decoded) => {
        //     console.log(decoded, 'decoded')
        //     signedRenewalInfo = decoded

        // });

        // res.send({
        //     signedTransactionInfo: signedTransactionInfo,
        //     signedRenewalInfo:signedRenewalInfo,
        // })

    } catch (error) {
        console.error('发送消耗信息时出错:', error.response?.data || error.message);
    }

})

async function sendConsumptionInfo(content) {
    const chinaTime = new Date();
    const jwtToken = createJWT(content.pk);
    try {
        const consumptionUrl = `https://api.storekit.itunes.apple.com/inApps/v1/transactions/consumption/${content.transaction_id}`;



        const ConsumptionRequest = {
            accountTenure: 0, //用户的账户年龄
            appAccountToken: '',//app生成的uuid
            consumptionStatus: 3,// 消费状况  1应用内购买不会被消耗 2应用内购买已部分消耗 3应用内购买已全部消耗
            customerConsented: true, //客户是否同意向 App Store 提供消费数据
            deliveryStatus: 0,// 0 app已提供商品，并运行正常 
            lifetimeDollarsPurchased: 0, // 0 未声明终身购买金额。
            lifetimeDollarsRefunded: 0, // 0 终身退款金额未申报。
            platform: 1, // 1苹果平台。
            playTime: 0, //用户使用app的时长  0未声明使用时间。
            refundPreference: 2, //希望拒绝退款
            sampleContentProvided: true, //是否提供试用
            userStatus: 1 //您的应用内客户帐户的状态。
        };
        const config = {
            headers: {
                Authorization: `Bearer ${jwtToken}`,
                'Content-Type': 'application/json',
            },
        };

        const response = await axios.put(consumptionUrl, ConsumptionRequest, config);
        console.log('退款回应成功:-----------------' + response.status + '----------' + content.transaction_id + '-----------' + content.pk + '------' + chinaTime.toLocaleString());
    } catch (error) {
        console.log('退款回应失败:-----------------' + content.transaction_id + '-----------' + content.pk + '------' + chinaTime.toLocaleString());

        console.error('退款回应:', error.response?.data || error.message);
    }
}

router.post('/getRefundByPkDay', async (req, res) => {
    const { pk, type } = req.body;
    // 设置时间为亚洲/上海的时区
    // 统一选择数据库函数
    const dbQueryFunc =
        pk === 'com.trans.translationAiAndSubtitle' || pk === 'com.TranslationAISubTitle'
            ? mysqlQueryMOMO
            : mysqlQuery;
    // 处理统计的公共方法
    const processResults = (results) => {
        if (results.length == 0) {
            return [];
        }

        const exchangeRates = {
            USD: 1.0,
            GBP: 0.800395,
            MXN: 20.5173,
            SAR: 3.75,
            EGP: 50.6195,
            IDR: 16356.7,
            BRL: 5.76580,
            QAR: 3.64,
            ZAR: 18.4602,
            MYR: 4.45725,
            CAD: 1.42566,
            ILS: 3.58665,
            JPY: 154.191,
            INR: 86.8679,
            AED: 3.67250,
            AUD: 1.58852,
            PHP: 58.1535,
            EUR: 0.958330,
            COP: 4165.95,
            PKR: 278.922,
            NZD: 1.76690,
            KZT: 497.910,
            CZK: 24.0070,
            NGN: 1505.03,
            RUB: 92.6978,
            CLP: 956.458,
            TRY: 36.1147,
            KRW: 1447.60,
            PEN: 3.71032,
            VND: 25475.7,
            CNY: 7.28512,
            DKK: 7.14790,
            HKD: 7.78616,
            CHF: 0.911281,
            BGN: 1.87423,
            TZS: 2594.69,
            PLN: 3.98441,
            SGD: 1.34773,
            RON: 4.76974,
            THB: 33.8423,
            NOK: 11.2195,
            SEK: 10.8155
        };

        const currencyMap = new Map();
        results.forEach(item => {
            if (item.is_agree == 2) {
                const currency = item.currency;
                const price = parseFloat(item.price) || 0;

                // 获取货币的汇率，若不存在汇率，则视为0，相当于价格直接为0
                const exchangeRate = exchangeRates[currency] || 0;
                const priceInUSD = exchangeRate !== 0 ? price / exchangeRate : 0;

                if (currencyMap.has(currency)) {
                    const existing = currencyMap.get(currency);
                    existing.price += price;
                    existing.price_in_usd += priceInUSD;
                    existing.count += 1;
                } else {
                    currencyMap.set(currency, {
                        currency: currency,
                        price: price,
                        price_in_usd: priceInUSD,
                        count: 1
                    });
                }
            }
        });

        return Array.from(currencyMap.values());
    };
    // For type '1', set the start to 3 days ago and end to now
    const threeDaysAgoStart = moment().tz('Asia/Shanghai').subtract(2, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
    const todayEnd = moment().tz('Asia/Shanghai').endOf('day').format('YYYY-MM-DD HH:mm:ss');
    let dateLimitString;

    switch (type) {
        case '1':
            const sql1 = "SELECT * FROM apple_refund_notifications WHERE pk = ? AND update_date BETWEEN ? AND ? AND is_agree != 0";
            try {
                const results = await dbQueryFunc(sql1, [pk, threeDaysAgoStart, todayEnd]);
                const data2 = processResults(results);

                // 计算总的美元金额
                let totalUSD = 0;
                if (data2.length > 0) {
                    totalUSD = data2.reduce((sum, item) => sum + item.price_in_usd, 0).toFixed(2);
                }

                // 返回到前端
                const response = {
                    data1: results,
                    data2: data2,
                    totalUSD: totalUSD
                };

                return res.json(response);
            } catch (error) {
                res.status(500).send(error.message);
            }
            break;
        case '2':
            dateLimitString = moment().tz('Asia/Shanghai').subtract(7, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
            break;
        case '3':
            dateLimitString = moment().tz('Asia/Shanghai').subtract(14, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
            break;
        case '4':
            dateLimitString = moment().tz('Asia/Shanghai').subtract(30, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
            break;
        case '5':
            dateLimitString = null; // 查找所有截至今天的数据
            break;
        default:
            return res.status(400).send('无效的类型');
    }

    if (type >= 2 && type <= 5) {
        const sql = type == 5
            ? "SELECT * FROM apple_refund_notifications WHERE pk = ? AND update_date <= ? AND is_agree != 0"
            : "SELECT * FROM apple_refund_notifications WHERE pk = ? AND update_date BETWEEN ? AND ? AND is_agree != 0";

        const params = type == 5
            ? [pk, todayEnd]
            : [pk, dateLimitString, todayEnd];

        try {
            const results = await mysqlQuery(sql, params);
            const data2 = processResults(results);
            // 计算总的美元金额
            let totalUSD = 0;
            if (data2.length > 0) {
                totalUSD = data2.reduce((sum, item) => sum + item.price_in_usd, 0).toFixed(2);
            }

            // 返回到前端
            const response = {
                data1: results,
                data2: data2,
                totalUSD: totalUSD
            };

            return res.json(response);


        } catch (error) {
            res.status(500).send(error.message);
        }
    }
});














async function mysqlQuery(sql, arr) {

    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
module.exports = router;
